sumifs using date and time values

M

Mr. Excel Wannabe

New Member
Joined
Mar 29, 2006
Messages
11
I have two columns of trip data, in column A are trip dates and column B are trip times. I want to sum trip times by month in hour groupings: before 5AM, between 5AM and 1PM, between 1PM and 9PM, and after 9PM. This is the formula I'm using for the Between 5 AM & 1 PM: =SUMIFS($B:$B, $A:$A, ">="&D$1, $A:$A, "<"&E$1, $B:$B, ">=TIME(5,0,0)", $B:$B, "<TIME(13,0,0)")

Column A is formatted as Date and Column B is formatted as Time. The results continue to show 0. Please tell me what I'm missing.

Pickup Date LocalPickup Time Local12/1/20231/1/20242/1/2024
12/1/20234:19:00 PMDecemberJanuaryFebruary
12/1/20232:37:00 PMBefore 8 AM000
12/1/20231:02:00 PMBetween 5 AM & 1 PM000
12/1/20233:15:00 PMBetween 1 PM & 9 PM000
12/1/20234:40:00 PMAfter 9 PM000
12/1/20232:00:00 PM
12/1/20239:13:00 AM
12/1/20238:41:00 AM
12/1/202312:35:00 PM
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
I think the problem is in the TIME portion of your formula. I put the times into a separate area and referred to them in the formula instead.

Book1
ABCDEFG
1Pickup Date LocalPickup Time Local01-Dec-2331-Jan-2429-Feb-24
201-Dec-2316:19DecemberJanuaryFebruary
301-Dec-2314:37Before 8 AM0:000:000:00
401-Dec-2313:02Between 5 AM & 1 PM30:290:000:00
501-Dec-2315:15Between 1 PM & 9 PM89:530:000:00
601-Dec-2316:40After 9 PM0:000:000:00
701-Dec-2314:00
801-Dec-2309:1305:00
901-Dec-2308:4108:00
1001-Dec-2312:3513:00
1121:00
1200:00
Sheet1
Cell Formulas
RangeFormula
E3:G6E3=SUMIFS($B:$B,$A:$A, ">="&E$1,$A:$A, "<"&F$1,$B:$B,">="&$D8,$B:$B, "<"&$D9)
 
Upvote 0
Edited to match your actual range descriptions in column D, and with the right months in row 1. Note the time format in E3:G6 = [h]:mm to display total hours instead of clock time.

2024-09-12.xlsx
ABCDEFG
1Pickup Date LocalPickup Time Local01-Dec-2301-Jan-2401-Feb-24
201-Dec-2316:19DecemberJanuaryFebruary
301-Dec-2314:37Before 8 AM0:000:000:00
401-Dec-2313:02Between 5 AM & 1 PM30:290:000:00
501-Dec-2315:15Between 1 PM & 9 PM89:530:000:00
601-Dec-2316:40After 9 PM0:000:000:00
701-Dec-2314:00
801-Dec-2309:1305:00
901-Dec-2308:4108:00
1001-Dec-2312:3513:00
1121:00
1200:00
13
Sheet1
Cell Formulas
RangeFormula
F1:G1F1=EDATE(E1,1)
E3:G3E3=SUMIFS($B:$B,$A:$A, ">="&E$1,$A:$A, "<"&F$1,$B:$B, "<"&$D9)
E4:G4E4=SUMIFS($B:$B,$A:$A, ">="&E$1,$A:$A, "<"&F$1,$B:$B,">="&$D8,$B:$B, "<"&$D10)
E5:G5E5=SUMIFS($B:$B,$A:$A, ">="&E$1,$A:$A, "<"&F$1,$B:$B,">="&$D10,$B:$B, "<"&$D11)
E6:G6E6=SUMIFS($B:$B,$A:$A, ">="&E$1,$A:$A, "<"&F$1,$B:$B,">="&$D11)
 
Upvote 0
Asbestos_Jen said:
I think the problem is in the TIME portion of your formula. I put the times into a separate area and referred to them in the formula instead.

Book1
ABCDEFG
1Pickup Date LocalPickup Time Local01-Dec-2331-Jan-2429-Feb-24
201-Dec-2316:19DecemberJanuaryFebruary
301-Dec-2314:37Before 8 AM0:000:000:00
401-Dec-2313:02Between 5 AM & 1 PM30:290:000:00
501-Dec-2315:15Between 1 PM & 9 PM89:530:000:00
601-Dec-2316:40After 9 PM0:000:000:00
701-Dec-2314:00
801-Dec-2309:1305:00
901-Dec-2308:4108:00
1001-Dec-2312:3513:00
1121:00
1200:00
Sheet1
Cell Formulas
RangeFormula
E3:G6E3=SUMIFS($B:$B,$A:$A, ">="&E$1,$A:$A, "<"&F$1,$B:$B,">="&$D8,$B:$B, "<"&$D9)
Click to expand...
What a simple yet wonderful solution. Thank you!
 
Upvote 0
For the life of me I'm still not getting the right solution here. I believe it's a formatting issue, but can't tell. I've dropped this into a Google Sheets file and would appreciate if you could isolate my error.

Pickup Date LocalPickup Time Local1-Dec-20231-Jan-20241-Feb-2024
12/1/202316:19DecemberJanuaryFebruary
12/1/202314:37Before 5 AM5:000:000:000:00
12/1/202313:02Between 8 AM & 1 PM8:000:000:000:00
12/1/202315:15Between 1 PM & 9 PM13:001:121:024:31
12/1/202316:40After 9 PM21:000:0018:060:00
12/1/202314:00Total1:1219:084:31
12/1/20239:13
12/1/20238:41
12/1/202312:35
12/1/202310:56
12/2/20239:45
12/2/202316:17
12/2/20238:51
12/2/202315:29

I'm using the formulas you provided.

Ultimately, I'll also be looking for a count of trips that happen during these timeframes. Would a countifs be easier?
 
Upvote 0
In your Google sheet, I changed the number format for E3:O7 to "duration" instead of time, which resulted in the values below.
I also added the following comment to E4 (December Between 8 AM & 1 PM): This formula is between 5:00 and 8:00; text says between 8:00 and 13:00. The row below is also set up with time ranges different than the descriptions. There is a gap between 13:00 and 21:00.
1-Dec-20231-Jan-20241-Feb-20241-Mar-20241-Apr-20241-May-20241-Jun-20241-Jul-20241-Aug-20241-Sep-2024
DecemberJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberTotal
Before 5 AM5:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:00
Between 8 AM & 1 PM8:000:00:000:00:000:00:0015:38:0015:37:0029:56:007:58:007:18:000:00:000:00:0076:27:00
Between 1 PM & 9 PM13:00553:12:00673:02:00724:31:00748:46:00764:48:00801:09:00396:21:00631:10:00581:42:000:00:005874:41:00
After 9 PM21:000:00:0042:06:000:00:000:00:0021:46:000:00:0021:04:000:00:000:00:000:00:0084:56:00
Total553:12:00715:08:00724:31:00764:24:00802:11:00831:05:00425:23:00638:28:00581:42:000:00:006036:04:00
 
Upvote 0
Solution
For the count of trips, what defines a trip? Is each pickup a separate trip? If so, use the same logic as the sumifs:
2024-09-12.xlsx
DEFGHIJKLMNOPQR
8Countifs05:00DecemberJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberTotal
95am-8am08:000010101002005
108am-1pm13:001161522232819192210170192
111pm-9pm21:002172414172915181713160182
12After 9pm00:001000001000002
13Total4334036415736373925330381
Sheet1
Cell Formulas
RangeFormula
F9:Q11F9=COUNTIFS($A:$A,">="&F$1,$A:$A,"<"&G$1,$B:$B,">="&$E8,$B:$B,"<"&$E9)
R9:R13R9=SUM(F9:Q9)
F12:Q12F12=COUNTIFS($A:$A,">="&F$1,$A:$A,"<"&G$1,$B:$B,">="&$E11)
F13:Q13F13=SUM(F9:F12)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
50
cooleomter
C
V
  • Locked
  • Question Question
If statement formula help between two times
Replies
2
Views
241
Fluff
Fluff
C
  • Question Question
Condition calculation
Replies
1
Views
344
shknbk2
S
J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
463
felixstraube
F
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
267
Cubist
Cubist

Forum statistics

Threads
1,223,705
Messages
6,173,996
Members
452,542
Latest member
Bricklin

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