ID: 11
Category: Excel Time
Question: How do I categorize a time duration into two different groups?
Answer: 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 endoftheactualcall, and then subtract (i.e., give
a credit) for the day/evening split for a call from midnight to the
startoftheactualcall.
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 daytime 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
=B5C5.
C5:D5 are copied down to C6:D6.
The formula in C7 is =C6C5+(B6<B5)*($C$3$C$2) and in D7
=D6D5+(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
=(C10MIN($C$3$C$2,MAX(C10$C$2,0)))(B10MIN($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.
ExtendedLink:
