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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
you can copy this directly, including formulas into a new workbook to play with:


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
39
40
41Defect by Product By Shift
42ABCD
43Holes2242
44Wrinkels2222
45odor1222
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))
C43:F45C43=SUM( ($A$3:$A$5=$B43)* (C$42&"-Team"=$B$2:$AR$2)* ($B$3:$AR$5>0) )
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
awoohaw said:
what version of excel are you using? Excel 2023 (saves as .xlsX file)


What do you want reported on regarding holes? # audits? #Defect #Borderline #NoDefect? yes Total # of holes, Total # of holes on A shift, B Shift, etc, Total Number fo defects (Sum of ABCD)
Click to expand...

Yes
 
Upvote 0
omer1234 said:
Yes
Click to expand...
Okay, look at what i've done above. This is a lot of back and forth. I think I've answered your intial questions, and you have information that you can work with that you can use to manipulate the formulas and create new ones.

I'm not really sure what you mean by Excel 2023. Functions available to that version are not available. Is that a Beta version? Or do you have 365?
 
Upvote 0
awoohaw said:
you can copy this directly, including formulas into a new workbook to play with:


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
39
40
41Defect by Product By Shift
42ABCD
43Holes2242
44Wrinkels2222
45odor1222
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))
C43:F45C43=SUM( ($A$3:$A$5=$B43)* (C$42&"-Team"=$B$2:$AR$2)* ($B$3:$AR$5>0) )
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
GREAT DUDE .... THANK YOU !!!!

Last thing..... Total # of Holes we inspected. So for examples

30 holes were found over the course of the month. Out of those defects were 10, (5 were on A shift, 1 were on C shift & 4 were D shift) B shift was defect free or 0

Thanks
 
Upvote 0
Y
awoohaw said:
Okay, look at what i've done above. This is a lot of back and forth. I think I've answered your intial questions, and you have information that you can work with that you can use to manipulate the formulas and create new ones.

I'm not really sure what you mean by Excel 2023. Functions available to that version are not available. Is that a Beta version? Or do you have 365?
Click to expand...
es you have been very helpful & i appreciate that
 
Upvote 0
omer1234 said:
GREAT DUDE .... THANK YOU !!!!

Last thing..... Total # of Holes we inspected. So for examples

30 holes were found over the course of the month. Out of those defects were 10, (5 were on A shift, 1 were on C shift & 4 were D shift) B shift was defect free or 0

Thanks
Click to expand...
As I said in an earlier post. The data I copied was truncated, so my solution only covers 4 days.
As far as counting total number of inspections regardless of audit status, change the formula in C43 to:
Excel Formula: 
=SUM( ($A$3:$A$5=$B43)* (C$42&"-Team"=$B$2:$AR$2)* ($B$3:$AR$5>=0) )
(If you have 365 you don't need to enter with the CSE keystroke).

Also, there are probably a few other functions in 365 that may make the formula more concise.

 
Upvote 0
I
awoohaw said:
you can copy this directly, including formulas into a new workbook to play with:


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
39
40
41Defect by Product By Shift
42ABCD
43Holes2242
44Wrinkels2222
45odor1222
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))
C43:F45C43=SUM( ($A$3:$A$5=$B43)* (C$42&"-Team"=$B$2:$AR$2)* ($B$3:$AR$5>0) )
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
I am getting #N/A error for my formula {=SUM((INT(Sheet1!$B$1:$IU$1)=$B6)*(C$3&"-Team"=Sheet1!C5:IU5))}

C11:F14C11=SUM((INT($B$1:$AR$1)=$B11)*(C$10&"-Team"=$B$2:$AR$2))


Total audits
A
2/27/2024​
#N/A​
 
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
93
Peter_SSs
Peter_SSs
J
  • Question Question
VBA: delete all rows where value (time) is less than 1 minute
2
Replies
13
Views
220
RobP
R
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
324
dshtesy
D
IluvPivots
  • Solved
Return date of next instance
2
Replies
10
Views
256
HighAndWilder
H
E
  • Question Question
If statement
Replies
4
Views
122
xlhelp15
xlhelp15

Forum statistics

Threads
1,223,992
Messages
6,175,826
Members
452,673
Latest member
LaMiaAvy

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