Count Times entry between hour

P

pbhange

New Member
Joined
Nov 9, 2017
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hello
want to count times entry between hours

Column A given 24 hours time want count like 11 PM to 12 AM how many entries done.

Times
05:34 AMTime StampCount
05:38 AM8 to 9PM
05:47 AM9 to 10PM
05:51 AM10 to 11PM
05:55 AM11 to 12AM
06:00 AM12 to 1AM
06:10 AM1 to 2AM
06:25 AM2 to 3AM
06:47 AM3 to 4AM
07:02 AM4 to 5AM
12:26:00 AM5 to 6AM
12:38:00 AM6 to 7AM
12:41:00 AM7 to 8AM
12:49:00 AM8 to 9AM
1:04:00 AM9 to 10AM
1:30:00 AM10 to 11AM
1:43:00 AM11 to 12PM
2:01:00 AM12 to 1PM
2:09:00 AM1 to 2PM
2:25:00 AM2 to 3PM
2:31:00 AM3 to 4PM
2:44:00 AM4 to 5PM
2:55:00 AM5 to 6PM
3:06:00 AM6 to 7PM
3:13:00 AM7 to 8PM
3:28:00 AM
3:34:00 AM
3:37:00 AM
4:10:00 AM
4:24:00 AM
4:30:00 AM
4:42:00 AM
5:00:00 AM
8:47:00 PM
9:03:00 PM
9:09:00 PM
9:30:00 PM
9:55:00 PM
10:18:00 PM
10:26:00 PM
10:30:00 PM
10:49:00 PM
11:21:00 PM
11:25:00 PM
11:47:00 PM
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
MrExcelPlayground11.xlsx
ABCD
1Times
25:34 AMStampCount
35:38 AM8:00 PM9:00 PM1
45:47 AM9:00 PM10:00 PM4
55:51 AM10:00 PM11:00 PM4
65:55 AM11:00 PM12:00 AM3
76:00 AM12:00 AM1:00 AM4
86:10 AM1:00 AM2:00 AM3
96:25 AM2:00 AM3:00 AM6
106:47 AM3:00 AM4:00 AM5
117:02 AM4:00 AM5:00 AM4
1212:26:00 AM5:00 AM6:00 AM6
1312:38:00 AM6:00 AM7:00 AM4
1412:41:00 AM7:00 AM8:00 AM1
1512:49:00 AM8:00 AM9:00 AM0
161:04:00 AM9:00 AM10:00 AM0
171:30:00 AM10:00 AM11:00 AM0
181:43:00 AM11:00 AM12:00 PM0
192:01:00 AM12:00 PM1:00 PM0
202:09:00 AM1:00 PM2:00 PM0
212:25:00 AM2:00 PM3:00 PM0
222:31:00 AM3:00 PM4:00 PM0
232:44:00 AM4:00 PM5:00 PM0
242:55:00 AM5:00 PM6:00 PM0
253:06:00 AM6:00 PM7:00 PM0
263:13:00 AM7:00 PM8:00 PM0
273:28:00 AM
283:34:00 AM
293:37:00 AM
304:10:00 AM
314:24:00 AM
324:30:00 AM
334:42:00 AM
345:00:00 AM
358:47:00 PM
369:03:00 PM
379:09:00 PM
389:30:00 PM
399:55:00 PM
4010:18:00 PM
4110:26:00 PM
4210:30:00 PM
4310:49:00 PM
4411:21:00 PM
4511:25:00 PM
4611:47:00 PM
Sheet28
Cell Formulas
RangeFormula
D3:D26D3=COUNTIFS($A$2:$A$46,">="&B3,$A$2:$A$46,"<"&C3)
 
Upvote 0
if you dont want or are unable to add the other columns, this will also work. it will work with the same time format you used in the OP as well as 24h time
---------------
Book1
ABCDE
1TimesTime StampAM/PMCount
25:34:008 to 9PM1
35:38:009 to 10PM4
45:47:0010 to 11PM4
55:51:0011 to 12AM3
65:55:0012 to 1AM4
76:00:001 to 2AM3
86:10:002 to 3AM6
96:25:003 to 4AM5
106:47:004 to 5AM4
117:02:005 to 6AM6
120:26:006 to 7AM4
130:38:007 to 8AM1
140:41:008 to 9AM0
150:49:009 to 10AM0
161:04:0010 to 11AM0
171:30:0011 to 12PM0
181:43:0012 to 1PM0
192:01:001 to 2PM0
202:09:002 to 3PM0
212:25:003 to 4PM0
222:31:004 to 5PM0
232:44:005 to 6PM0
242:55:006 to 7PM0
253:06:007 to 8PM0
263:13:00
273:28:00
283:34:00
293:37:00
304:10:00
314:24:00
324:30:00
334:42:00
345:00:00
3520:47:00
3621:03:00
3721:09:00
3821:30:00
3921:55:00
4022:18:00
4122:26:00
4222:30:00
4322:49:00
4423:21:00
4523:25:00
4623:47:00
Sheet1
Cell Formulas
RangeFormula
E2E2=COUNTIFS(A:A,">=20:00",A:A,"<21:00")
E3E3=COUNTIFS(A:A,">=21:00",A:A,"<22:00")
E4E4=COUNTIFS(A:A,">=22:00",A:A,"<23:00")
E5E5=COUNTIFS(A:A,">=23:00",A:A,"<24:00")
E6E6=COUNTIFS(A:A,">=0:00",A:A,"<1:00")
E7E7=COUNTIFS(A:A,">=1:00",A:A,"<2:00")
E8E8=COUNTIFS(A:A,">=2:00",A:A,"<3:00")
E9E9=COUNTIFS(A:A,">=3:00",A:A,"<4:00")
E10E10=COUNTIFS(A:A,">=4:00",A:A,"<5:00")
E11E11=COUNTIFS(A:A,">=5:00",A:A,"<6:00")
E12E12=COUNTIFS(A:A,">=6:00",A:A,"<7:00")
E13E13=COUNTIFS(A:A,">=7:00",A:A,"<8:00")
E14E14=COUNTIFS(A:A,">=8:00",A:A,"<9:00")
E15E15=COUNTIFS(A:A,">=9:00",A:A,"<10:00")
E16E16=COUNTIFS(A:A,">=10:00",A:A,"<11:00")
E17E17=COUNTIFS(A:A,">=11:00",A:A,"<12:00")
E18E18=COUNTIFS(A:A,">=12:00",A:A,"<13:00")
E19E19=COUNTIFS(A:A,">=13:00",A:A,"<14:00")
E20E20=COUNTIFS(A:A,">=14:00",A:A,"<15:00")
E21E21=COUNTIFS(A:A,">=15:00",A:A,"<16:00")
E22E22=COUNTIFS(A:A,">=16:00",A:A,"<17:00")
E23E23=COUNTIFS(A:A,">=17:00",A:A,"<18:00")
E24E24=COUNTIFS(A:A,">=18:00",A:A,"<19:00")
E25E25=COUNTIFS(A:A,">=19:00",A:A,"<20:00")
 
Last edited:
Upvote 0
thanks working but want to calculate for multiple criteria

End TimeName
6:57 PMSam
7:11 PMSam
7:26 PMSam
8:05 PMJoySelect NameSam
8:17 PMJoy
8:49 PMJoyTime StampHourly Count
9:00 PMJoy8 to 9PM0
9:21 PMJoy9 to 10PM0
9:57 PMJoy10 to 11PM0
9:58 PMJoy11 to 12AM0
10:07 PMJoy12 to 1AM0
10:23 PMJoy1 to 2AM0
10:28 PMJoy2 to 3AM0
10:46 PMJoy3 to 4AM0
11:07 PMJoy4 to 5AM0
11:11 PMJoy5 to 6AM0
11:14 PMJoy6 to 7AM0
11:33 PMJoy7 to 8AM0
11:40 PMJoy8 to 9AM3
11:47 PMJoy9 to 10AM3
11:50 PMJoy10 to 11AM2
11:54 PMJoy11 to 12PM3
12:01 AMJoy12 to 1PM2
12:15 AMJoy1 to 2PM3
12:48 AMJoy2 to 3PM4
1:12 AMJoy3 to 4PM7
1:37 AMJoy4 to 5PM7
1:44 AMJoy5 to 6PM5
2:00 AMJoy6 to 7PM4
2:18 AMJoy7 to 8PM2
2:33 AMJoy
2:44 AMJoy
2:50 AMJoy
3:00 AMJoy
3:07 AMJoy
3:10 AMJoy
3:25 AMJoy
3:29 AMJoy
3:33 AMJoy
3:51 AMJoy
4:01 AMJoy
4:09 AMJoy
4:12 AMJoy
4:19 AMJoy
4:23 AMJoy
4:27 AMJoy
4:34 AMJoy
 
Upvote 0
This is just how countifs works:
MrExcelPlayground11.xlsx
ABCDE
1TimesSam
26:57 PMSamStampCount
37:11 PMSam8:00 PM9:00 PM0
47:26 PMSam9:00 PM10:00 PM0
58:05 PMJoy10:00 PM11:00 PM0
68:17 PMJoy11:00 PM12:00 AM0
78:49 PMJoy12:00 AM1:00 AM0
89:00 PMJoy1:00 AM2:00 AM0
99:21 PMJoy2:00 AM3:00 AM0
109:57 PMJoy3:00 AM4:00 AM0
119:58 PMJoy4:00 AM5:00 AM0
1210:07 PMJoy5:00 AM6:00 AM0
1310:23 PMJoy6:00 AM7:00 AM0
1410:28 PMJoy7:00 AM8:00 AM0
1510:46 PMJoy8:00 AM9:00 AM0
1611:07 PMJoy9:00 AM10:00 AM0
1711:11 PMJoy10:00 AM11:00 AM0
1811:14 PMJoy11:00 AM12:00 PM0
1911:33 PMJoy12:00 PM1:00 PM0
2011:40 PMJoy1:00 PM2:00 PM0
2111:47 PMJoy2:00 PM3:00 PM0
2211:50 PMJoy3:00 PM4:00 PM0
2311:54 PMJoy4:00 PM5:00 PM0
2412:01 AMJoy5:00 PM6:00 PM0
2512:15 AMJoy6:00 PM7:00 PM1
2612:48 AMJoy7:00 PM8:00 PM2
271:12 AMJoy
281:37 AMJoy
291:44 AMJoy
302:00 AMJoy
312:18 AMJoy
322:33 AMJoy
332:44 AMJoy
342:50 AMJoy
353:00 AMJoy
363:07 AMJoy
373:10 AMJoy
383:25 AMJoy
393:29 AMJoy
403:33 AMJoy
413:51 AMJoy
424:01 AMJoy
434:09 AMJoy
444:12 AMJoy
454:19 AMJoy
464:23 AMJoy
474:27 AMJoy
484:34 AMJoy
Sheet28
Cell Formulas
RangeFormula
E3:E26E3=COUNTIFS($A$2:$A$48,">="&C3,$A$2:$A$48,"<"&D3,$B$2:$B$48,$C$1)
 
Upvote 0
above formula working but unable to between 11 PM to 12 AM for joy
 
Upvote 0
Right. Sometimes 12:00am is 0, sometimes it's one. In this case, the in 11-12 it should be 1, but on the next line, it should be zero.
MrExcelPlayground11.xlsx
ABCDE
1TimesJoy
26:57 PMSamStampCount
37:11 PMSam8:00 PM9:00 PM3
47:26 PMSam9:00 PM10:00 PM4
58:05 PMJoy10:00 PM11:00 PM4
68:17 PMJoy11:00 PM12:00 AM8
78:49 PMJoy12:00 AM1:00 AM3
89:00 PMJoy1:00 AM2:00 AM3
99:21 PMJoy2:00 AM3:00 AM5
109:57 PMJoy3:00 AM4:00 AM7
119:58 PMJoy4:00 AM5:00 AM7
1210:07 PMJoy5:00 AM6:00 AM0
1310:23 PMJoy6:00 AM7:00 AM0
1410:28 PMJoy7:00 AM8:00 AM0
1510:46 PMJoy8:00 AM9:00 AM0
1611:07 PMJoy9:00 AM10:00 AM0
1711:11 PMJoy10:00 AM11:00 AM0
1811:14 PMJoy11:00 AM12:00 PM0
1911:33 PMJoy12:00 PM1:00 PM0
2011:40 PMJoy1:00 PM2:00 PM0
2111:47 PMJoy2:00 PM3:00 PM0
2211:50 PMJoy3:00 PM4:00 PM0
2311:54 PMJoy4:00 PM5:00 PM0
2412:01 AMJoy5:00 PM6:00 PM0
2512:15 AMJoy6:00 PM7:00 PM0
2612:48 AMJoy7:00 PM8:00 PM0
271:12 AMJoy
281:37 AMJoy
291:44 AMJoy
302:00 AMJoy
312:18 AMJoy
322:33 AMJoy
332:44 AMJoy
342:50 AMJoy
353:00 AMJoy
363:07 AMJoy
373:10 AMJoy
383:25 AMJoy
393:29 AMJoy
403:33 AMJoy
413:51 AMJoy
424:01 AMJoy
434:09 AMJoy
444:12 AMJoy
454:19 AMJoy
464:23 AMJoy
474:27 AMJoy
484:34 AMJoy
Sheet28
Cell Formulas
RangeFormula
E3:E26E3=COUNTIFS($A$2:$A$48,">="&C3,$A$2:$A$48,"<"&D3,$B$2:$B$48,$C$1)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Question
Count #of Employees Worked Between Time Periods
Replies
2
Views
322
Eric W
Eric W
C
  • Question
Condition calculation
Replies
1
Views
169
shknbk2
S
Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
358
Zee996
Z
C
  • Solved
Calculate difference b/w numbers
Replies
3
Views
176
Peter_SSs
Peter_SSs
A
  • Question
Hi everyone. Help wit Excel!
Replies
2
Views
149
StephenCrump
S

Forum statistics

Threads
1,216,082
Messages
6,128,716
Members
449,464
Latest member
againofsoul

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