This answer is provided by Tushar Mehta

ID: 11

Category: Excel Time

Question: How do I categorize a time duration into two different groups?


The easiest way, at least for me, to do such calculations is to use a 'base reference', typically midnight, and calculate all numbers from that reference. So, to calculate the time for a call, one calculates the day/evening split for a call from midnight to the end-of-the-actual-call, and then subtract (i.e., give a credit) for the day/evening split for a call from midnight to the start-of-the-actual-call.

How does it work in practice? Consider the layout below:

  B C D
2 Start Day 08:00
3 End Day 18:00
4 Call Data Day Even
5 17:00 09:00 08:00
6 16:00 08:00 08:00
7 Charge 09:00 14:00

Start Day and End Day identify the thresholds for the day-time calls. The start time for a call is in B5, the end time is in B6. The assumption is that if the end time is less than the start time, the call crossed midnight. The above call, for example, was from 5:00 pm one day to 4:00 pm the *next* day.

The formula in C5 is =MIN($C$3-$C$2,MAX(B5-$C$2,0)) and the formula in D5 is =B5-C5.

C5:D5 are copied down to C6:D6.
The formula in C7 is =C6-C5+(B6<B5)*($C$3-$C$2) and in D7 =D6-D5+(B6<B5)*(1-($C$3-$C$2)). The last part in C7 (and D7) is a correction for calls that cross midnight.

The Min(...,Max(...)) formula might be easier to understand in its IF() form, since it's identical to =IF(B5>$C$3,$C$3-$C$2,IF(B5>$C$2,B5-$C$2,0)).

Of course, the whole thing can be collapsed into a single formula for 'day' and a single formula for 'evening' as below.

  B C D E
9 Start End Day Even
10 07:59 18:01 10:00 00:02

where D10 contains =MIN($C$3-$C$2,MAX(C10-$C$2,0))-MIN($C$3-$C$2,MAX(B10-$C$2,0))+(C10<B10)*($C$3-$C$2)

and E10 contains =(C10-MIN($C$3-$C$2,MAX(C10-$C$2,0)))-(B10-MIN($C$3-$C$2,MAX(B10-$C$2,0)))+(C10<B10)*(1-($C$3-$C$2)). These formulas are just a mechanical combination of the formulas C5:C7 and D5:D7 respectively. Of course, they were adjusted to refer to B10 and C10 rather than B5 and B6.


Website material copyright 2003-2006 TM Faculty Associates