Home |
Search |
Today's Posts |
#1
|
|||
|
|||
'Time to Fix' Formula
I am trying to create a formular which will exclude out of hour support time.. Here's an example Device A fails at 25/08/05 05:20:00 and recovers on 30/08/05 11:20:00 The total down time is 126:00:00 However The total fix time is 61:20:00 because the hours between 18:00:00 - 05:59:59 are excluded from the time to fix figure for each day.. I need a formular which will look at the Fail time and the recovery time and produce a Fix time, rather than a total down time. Can anyone help ????? -- wlln001 ------------------------------------------------------------------------ wlln001's Profile: http://www.excelforum.com/member.php...o&userid=27022 View this thread: http://www.excelforum.com/showthread...hreadid=437381 |
#2
|
|||
|
|||
wlln001
I think I have a formula that will do what you ask. Device A fails at 25/08/05 05:20:00 and recovers on 30/08/05 11:20:00 The total fix time is 61:20:00 because the hours between 18:00:00 - 05:59:59 are excluded from the time to fix figure for each day.. I actually make the answer 65:20:00 5x12hours + 5:20 Defined names StartDT and EndDT as Fail and Recovery date times DayStart and DayEnd are the working time eg. 06:00:00 and 18:00:00 =((INT(EndDT)-INT(StartDT))*(DayEnd-DayStart)) -MAX(MIN(MOD(StartDT,1)-DayStart,(DayEnd-DayStart)),0) +MAX(MIN(MOD(EndDT,1)-DayStart,(DayEnd-DayStart)),0) I am sure the logic could be simplified but this seems to work. There are some very good generic formulas that can be found with a Google search for Working Time that will allow excluding weekends and holidays using NETWORKDAYS. hth RES |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to deduct unpaid breaks in time sheet | Excel Discussion (Misc queries) | |||
Formula Needed for Transit Time | Excel Discussion (Misc queries) | |||
Need formula to add increments of 0.16 seconds to time | Excel Discussion (Misc queries) | |||
Formula for time? | Excel Discussion (Misc queries) | |||
What is the formula for getting time difference e.g. ("4 hrs 15 m. | Charts and Charting in Excel |