| 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 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.
ExtendedLink:
|