Count Dates

O

omer1234

Board Regular
Joined
Nov 29, 2011
Messages
161
Hi,

I have dates in Sheet 1

Column A - 2/27/2024 10:29:03 AM
Column B - 2/27/2024 10:26:33 AM
Column C - 2/27/2024 10:25:55 AM
Column D - 2/26/2024 7:50:50 AM
Column E - 2/25/2024 7:50:50 AM

How do I make excel count the dates ignoring the time stamp?

So it should say I have 3 entries for (2/27), 1 entry for (2/26) and 1 entry for (2/25).

Thanks
 
Sort by date Sort by votes
awoohaw said:
Select your range. Right Click Copy, or CNTL-C, Come to this web page, start a message, click V.

if you want to post a file, you need to share it to Dropbox or OneDrive. Then share the link.
Click to expand...



This is sheet 2, these are the formulas I need help with ( Not sure if the yellow comes through) but kindly look at the images I posted....... I am still trying to upload the actual file so you can have that to play with



Total # of audits completed
A-Team
1/1/2024​
2/1/2024​
3/1/2024​
B-Team
1/2/2024​
2/2/2024​
3/2/2024​
C-Team
1/3/2024​
2/3/2024​
3/3/2024​
D-Team
1/4/2024​
2/4/2024​
3/4/2024​
1/5/2024​
2/5/2024​
3/5/2024​
1/6/2024​
2/6/2024​
3/6/2024​
1/7/2024​
2/7/2024​
3/7/2024​
1/8/2024​
2/8/2024​
3/8/2024​
No defct Total
1/9/2024​
2/9/2024​
3/9/2024​
Borderline
1/10/2024​
2/10/2024​
3/10/2024​
Defect total
1/11/2024​
2/11/2024​
3/11/2024​
1/12/2024​
2/12/2024​
3/12/2024​
1/13/2024​
2/13/2024​
3/13/2024​
1/14/2024​
2/14/2024​
3/14/2024​
A shift (1)
1/15/2024​
2/15/2024​
3/15/2024​
A shift (3)
1/16/2024​
2/16/2024​
3/16/2024​
A shift (0)
1/17/2024​
2/17/2024​
3/17/2024​
1/18/2024​
2/18/2024​
3/18/2024​
B shift (1)
1/19/2024​
2/19/2024​
3/19/2024​
B shift (3)
1/20/2024​
2/20/2024​
3/20/2024​
B shift (0)
1/21/2024​
2/21/2024​
3/21/2024​
1/22/2024​
2/22/2024​
3/22/2024​
C shift (1)
1/23/2024​
2/23/2024​
​
3/23/2024​
C shift (3)
1/24/2024​
2/24/2024​
​
3/24/2024​
C shift (0)
1/25/2024​
2/25/2024​
​
3/25/2024​
1/26/2024​
2/26/2024​
​
3/26/2024​
1/27/2024​
2/27/2024​
3/27/2024​
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
What do you want to count here? You have two threads using the same data. Don't worry now about the file, unless you think it will be a really big help.

Do you have two questions to the forum or one? And please restate what you want.
 
Upvote 0
awoohaw said:
What do you want to count here? You have two threads using the same data. Don't worry now about the file, unless you think it will be a really big help.

Do you have two questions to the forum or one? And please restate what you want.
Click to expand...


I want to count total # of defects on each shift

Then a breakdown by How many 3 there were on A,B,C shift (Meaning not many defects based on data in sheet 1)
How many borderline issues were on A,B,C shift

The dates to the right , I want to know how many audits were completed on that particular day
 
Upvote 0
Okay, in the first table you posted the far right columns had dates that were all "####", you don't have to repost the data, I'll just ignore those columns, you have enough columns for me to work with. I just want you to be aware an answer I give you may not match the expectation using all the data you posted.

How many borderline issues were on A,B,C shift
Click to expand...
What defines a "Shift"?

in the second posting table, what do these 3 rows mean? Also, what do the blank rows you have mean?

No defct Total1/9/20242/9/20243/9/2024
Borderline1/10/20242/10/20243/10/2024
Defect total1/11/20242/11/20243/11/2024
1/12/20242/12/20243/12/2024
 
Upvote 0
awoohaw said:
Okay, in the first table you posted the far right columns had dates that were all "####", you don't have to repost the data, I'll just ignore those columns, you have enough columns for me to work with. I just want you to be aware an answer I give you may not match the expectation using all the data you posted.


What defines a "Shift"? Sheet 1, Row 2 is what shift the even happened on.

in the second posting table, what do these 3 rows mean? the 3 rows define what a 1 means vs what a 3 means in the table above (Row3,4,5)
Click to expand...



awoohaw said:
Also, what do the blank rows you have mean? Take a look at my snip it, I am struggling with formulas for everything in yellow

No defct Total1/9/20242/9/20243/9/2024
Borderline1/10/20242/10/20243/10/2024
Defect total1/11/20242/11/20243/11/2024
1/12/20242/12/20243/12/2024
Click to expand...
 

Attachments

  • Sheet 2.JPG
    Sheet 2.JPG
    124.7 KB · Views: 7
Upvote 0
i cannot determine what you want in the yellow columns? What cells have formulas now, and what are the formulas ?

So, is a shift equivalent to one day?
 
Upvote 0
Well, this is what my guess is. I completely ignored your 2nd table as it made no sense to me.
But this give count of the different audit status for each team for each day.
This could be done very easily with a pivot table if your data was in a flat format of Four columns
DateTeamAudit ScoreAudited Item


Note: You may need to enter the formulas using the CNTL-SHFT-ENTR (CSE) keystroke. Or even change the SUM function to SUMPRODUCT.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1Date/Time Entered2024-02-27 10:29:032024-02-27 10:26:332024-02-27 09:57:292024-02-27 08:30:532024-02-27 08:20:242024-02-27 08:18:292024-02-27 08:15:572024-02-27 08:14:252024-02-21 12:30:592024-02-21 11:18:572024-02-21 11:16:352024-02-21 11:15:332024-02-21 11:13:322024-02-20 12:57:302024-02-20 12:53:172024-02-20 12:50:302024-02-20 12:47:402024-02-20 12:44:422024-02-20 12:38:472024-02-20 12:34:212024-02-20 12:09:412024-02-20 12:08:102024-02-20 12:06:292024-02-20 12:05:302024-02-20 12:03:582024-02-20 12:01:262024-02-20 11:59:262024-02-20 08:08:412024-02-20 08:07:282024-02-20 08:05:482024-02-20 08:01:132024-02-19 15:30:542024-02-19 15:27:572024-02-19 15:26:482024-02-19 15:25:102024-02-19 15:22:212024-02-19 15:20:462024-02-19 15:12:292024-02-19 14:42:472024-02-19 14:40:312024-02-19 14:38:012024-02-19 14:36:212024-02-19 14:34:03
2TeamA-TeamD-TeamA-TeamD-TeamD-TeamD-TeamD-TeamD-TeamA-TeamD-TeamD-TeamD-TeamD-TeamC-TeamC-TeamC-TeamC-TeamC-TeamC-TeamC-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamC-TeamC-TeamC-TeamC-TeamC-Team
3Holes0033000030003030030030000000000000003030003
4Wrinkels0033000010003030000030000000000000003030000
5odor0033000000003030000030000000000000003030000
61
73 means there was a defect
80 means no defect
91 means borderlineTotal Audits
10ABCD
112024-02-272006
122024-02-211004
132024-02-2001170
142024-02-190750
15
16
17Total Audits w defect(Code:3.00TRUE
18ABCD
192024-02-273003
202024-02-211003
212024-02-200340
222024-02-190340
23
24
25Total Borderline(Code:1.00FALSE
26ABCD
272024-02-270000
282024-02-211000
292024-02-200000
302024-02-190000
31
32
33Total Audits w no defect(Code:0.00TRUE
34ABCD
352024-02-2730015
362024-02-211009
372024-02-20030170
382024-02-19018110
Sheet3
Cell Formulas
RangeFormula
C11:F14C11=SUM((INT($B$1:$AR$1)=$B11)*(C$10&"-Team"=$B$2:$AR$2))
F17,F33,F25F17=E17=D3
C19:F22C19=SUM((INT($B$1:$AR$1)=$B19)*(C$18&"-Team"=$B$2:$AR$2)*($B$3:$AR$5=$E$17))
C27:F30C27=SUM((INT($B$1:$AR$1)=$B27)*(C$26&"-Team"=$B$2:$AR$2)*($B$3:$AR$5=$E$25))
C35:F38C35=SUM((INT($B$1:$AR$1)=$B35)*(C$34&"-Team"=$B$2:$AR$2)*($B$3:$AR$5=$E$33))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
awoohaw said:
i cannot determine what you want in the yellow columns? What cells have formulas now, and what are the formulas ?

So, is a shift equivalent to one day?
Click to expand...
I have no formulas man, I am new to excel... I tried to google formulas and they didnt work. So I need help
 
Upvote 0
G
awoohaw said:
Well, this is what my guess is. I completely ignored your 2nd table as it made no sense to me.
But this give count of the different audit status for each team for each day.
This could be done very easily with a pivot table if your data was in a flat format of Four columns
DateTeamAudit ScoreAudited Item


Note: You may need to enter the formulas using the CNTL-SHFT-ENTR (CSE) keystroke. Or even change the SUM function to SUMPRODUCT.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1Date/Time Entered2024-02-27 10:29:032024-02-27 10:26:332024-02-27 09:57:292024-02-27 08:30:532024-02-27 08:20:242024-02-27 08:18:292024-02-27 08:15:572024-02-27 08:14:252024-02-21 12:30:592024-02-21 11:18:572024-02-21 11:16:352024-02-21 11:15:332024-02-21 11:13:322024-02-20 12:57:302024-02-20 12:53:172024-02-20 12:50:302024-02-20 12:47:402024-02-20 12:44:422024-02-20 12:38:472024-02-20 12:34:212024-02-20 12:09:412024-02-20 12:08:102024-02-20 12:06:292024-02-20 12:05:302024-02-20 12:03:582024-02-20 12:01:262024-02-20 11:59:262024-02-20 08:08:412024-02-20 08:07:282024-02-20 08:05:482024-02-20 08:01:132024-02-19 15:30:542024-02-19 15:27:572024-02-19 15:26:482024-02-19 15:25:102024-02-19 15:22:212024-02-19 15:20:462024-02-19 15:12:292024-02-19 14:42:472024-02-19 14:40:312024-02-19 14:38:012024-02-19 14:36:212024-02-19 14:34:03
2TeamA-TeamD-TeamA-TeamD-TeamD-TeamD-TeamD-TeamD-TeamA-TeamD-TeamD-TeamD-TeamD-TeamC-TeamC-TeamC-TeamC-TeamC-TeamC-TeamC-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamB-TeamC-TeamC-TeamC-TeamC-TeamC-Team
3Holes0033000030003030030030000000000000003030003
4Wrinkels0033000010003030000030000000000000003030000
5odor0033000000003030000030000000000000003030000
61
73 means there was a defect
80 means no defect
91 means borderlineTotal Audits
10ABCD
112024-02-272006
122024-02-211004
132024-02-2001170
142024-02-190750
15
16
17Total Audits w defect(Code:3.00TRUE
18ABCD
192024-02-273003
202024-02-211003
212024-02-200340
222024-02-190340
23
24
25Total Borderline(Code:1.00FALSE
26ABCD
272024-02-270000
282024-02-211000
292024-02-200000
302024-02-190000
31
32
33Total Audits w no defect(Code:0.00TRUE
34ABCD
352024-02-2730015
362024-02-211009
372024-02-20030170
382024-02-19018110
Sheet3
Cell Formulas
RangeFormula
C11:F14C11=SUM((INT($B$1:$AR$1)=$B11)*(C$10&"-Team"=$B$2:$AR$2))
F17,F33,F25F17=E17=D3
C19:F22C19=SUM((INT($B$1:$AR$1)=$B19)*(C$18&"-Team"=$B$2:$AR$2)*($B$3:$AR$5=$E$17))
C27:F30C27=SUM((INT($B$1:$AR$1)=$B27)*(C$26&"-Team"=$B$2:$AR$2)*($B$3:$AR$5=$E$25))
C35:F38C35=SUM((INT($B$1:$AR$1)=$B35)*(C$34&"-Team"=$B$2:$AR$2)*($B$3:$AR$5=$E$33))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
Great start.....So how many holes happened on A, B, C, D shift regardless of date A3? A4, A5
 
Upvote 0
omer1234 said:
G

reat start.....So how many holes happened on A, B, C, D shift regardless of date A3? A4, A5
Click to expand...
what version of excel are you using?


What do you want reported on regarding holes? # audits? #Defect #Borderline #NoDefect?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
J
  • Question Question
VBA: delete all rows where value (time) is less than 1 minute
2
Replies
13
Views
230
RobP
R
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
126
flashgordie
F
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
IluvPivots
  • Solved
Return date of next instance
2
Replies
10
Views
270
HighAndWilder
H

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top