If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to deduct unpaid breaks in time sheet | Rick | General Discussion | 3 | August 26th, 2005 11:53 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
time formula question... | Greg | General Discussion | 5 | February 25th, 2005 10:11 AM |
formula for adding time to time eg 90min to 6.30am | Vickijo60 | General Discussion | 4 | August 31st, 2004 11:54 AM |
Time Formula Help | John | General Discussion | 4 | July 29th, 2004 09:33 PM |