Hello Excelsior's!
I am trying to write a formula to collect a subtotal of sales based off time periods. EG: Breakfast 6am - 11am // Lunch 12:00pm - 15:00pm // Dinner 17:00 - 00:00
Giving me FOOD & BEVERAGE total for the above periods
I was going down the Line of using a SUMIF function.
=SUMIF(A:A,A2>"06:00"&A2<"11:00",C:C)
I know i may have to use the TEXT(A2,HH:MM) to format to the time stamp only, however if i can avoid no changing the format at all. That would be an advantage.
<tbody>
</tbody>
I have tried a few other functions but returning to value of a blank timestamp //
<tbody>
</tbody>
Any help would be awesome!
My first Post, so apologies if my etiquette is off!
_melvin_
I am trying to write a formula to collect a subtotal of sales based off time periods. EG: Breakfast 6am - 11am // Lunch 12:00pm - 15:00pm // Dinner 17:00 - 00:00
Giving me FOOD & BEVERAGE total for the above periods
I was going down the Line of using a SUMIF function.
=SUMIF(A:A,A2>"06:00"&A2<"11:00",C:C)
I know i may have to use the TEXT(A2,HH:MM) to format to the time stamp only, however if i can avoid no changing the format at all. That would be an advantage.
Date | Food | Beverage | |
<tbody> </tbody> | $7.60 | ||
26/02/2018 10:37 | $13.60 | ||
26/02/2018 12:38 | $5.80 | ||
26/02/2018 1:02 | $15.40 | ||
26/02/2018 18:30 | $30.00 | ||
26/02/2018 19:20 | $50.00 | ||
BREAKFAST | =SUMIF(A:A,A2>"06:00"&A2<"11:00",B:B) | =SUMIF(A:A,A2>"06:00"&A2<"11:00",C:C) | |
LUNCH | =SUMIF(A:A,A2>"11:00"&A2<"16:00",B:B) | =SUMIF(A:A,A2>"11:00"&A2<"16:00",C:C) | |
DINNER | =SUMIF(A:A,A2>"16:00"&A2<"00:00",B:B) | =SUMIF(A:A,A2>"16:00"&A2<"00:00",C:C) |
<tbody>
</tbody>
I have tried a few other functions but returning to value of a blank timestamp //
00/01/1900 00:00 |
<tbody>
</tbody>
Any help would be awesome!
My first Post, so apologies if my etiquette is off!
_melvin_