Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated Time Formula
This is a somewhat late response to a posting 27-Dec-2003
http://groups.google.co.nz/groups?q=...0a%40phx.gb l 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated Time Formula
Missed something... (why does that always happen?)
C3: =TIME(HOUR(A3),MINUTE(A3),SECOND(A3)) D3: =TIME(HOUR(B3),MINUTE(B3),SECOND(B3)) Rob "Rob van Gelder" wrote in message ... This is a somewhat late response to a posting 27-Dec-2003 http://groups.google.co.nz/groups?q=...0a%40phx.gb l 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,$D 3< =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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Formula help please | Excel Discussion (Misc queries) | |||
Complicated Formula - I think | Excel Worksheet Functions | |||
Time formula, complicated? | Excel Discussion (Misc queries) | |||
Complicated IF Formula | Excel Worksheet Functions | |||
Complicated Time Formula | Excel Programming |