Complicated Time Formula

[Rob van Gelder]

This is a somewhat late response to a posting 27-Dec-2003
http://groups.google.co.nz/groups?q...8&[email protected]

Jay's original question:

I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that contain
a start time and an end time. I need to calculate the
total time between the two times and place the value into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.The one I wanted to solve for myself was: Given

a list of service downtime events, how many business hours have been
affected?

I've developed a fairly generic worksheet formula for figuring it out:

A3: 4-Jan-2004 14:30:00
B3: 5-Jan-2004 09:00:00
E1: 00:00
E2: =E1+(1/24)
E3:
=IF(OR(AND($C3<=$D3,$D3<=E$1),AND($C3>=E$2,OR($C3<=$D3,$D3<=E$1))),0,IF(AND(
$D3<=E$2,$D3>=E$1,OR($C3<=E$1,$C3>=E$2)),$D3-E$1,IF(OR(AND($C3<=E$1,$D3>=E$2
),AND($C3>=$D3,OR($C3<=E$1,$D3>=E$2))),E$2-E$1,IF(AND($C3<=$D3,$C3>=E$1,$D3<
=E$2),$D3-$C3,IF(AND($C3>=E$1,$C3<=E$2,OR($D3<=E$1,$D3>=E$2)),E$2-$C3,($D3-E
$1)+(E$2-$C3))))))+INT($B3-$A3)*(E$2-E$1)
No spaces in the formula.

Highlight E1:E3 and fill across to column AB.

Optionally custom number format Row 3 as [m] (to show 60 instead of 1:00)

Some assumptions:
1. A3 will be less than or equal to A4
2. E2 will be less than E3
3. If E3 (or E4, E5, etc...) is 00:00 then it should be 00:00 + 1 or else
assumption 2 won't work.



Rob

 

[Rob van Gelder]

Missed something... (why does that always happen?)

C3: =TIME(HOUR(A3),MINUTE(A3),SECOND(A3))
D3: =TIME(HOUR(B3),MINUTE(B3),SECOND(B3))

Rob

This is a somewhat late response to a posting 27-Dec-2003
http://groups.google.co.nz/groups?q...8&[email protected]

Jay's original question: Given
a list of service downtime events, how many business hours have been
affected?

I've developed a fairly generic worksheet formula for figuring it out:

A3: 4-Jan-2004 14:30:00
B3: 5-Jan-2004 09:00:00
E1: 00:00
E2: =E1+(1/24)
E3:


3<

$1)+(E$2-$C3))))))+INT($B3-$A3)*(E$2-E$1)
No spaces in the formula.

Highlight E1:E3 and fill across to column AB.

Optionally custom number format Row 3 as [m] (to show 60 instead of 1:00)

Some assumptions:
1. A3 will be less than or equal to A4
2. E2 will be less than E3
3. If E3 (or E4, E5, etc...) is 00:00 then it should be 00:00 + 1 or else
assumption 2 won't work.



Rob