I am looking for a formula that will calculate the elapsed days, hours, and minutes, excluding non-working hours, weekends, and holidays. This is for response times in resolving customer issues received on our Help Desk. I have looked at the NETWORKDAYS function and also the formula provided in another string for calculating the same scenario, minus the non-work hours. The formula I am using to calculate elapsed time is:
=IF(D2<A2,"-"&TEXT(A2-D2,"h:mm"),NETWORKDAYS(A2,D2,L$2:L$6)-1-(MOD(D2,1)<MOD(A2,1))&" days "&TEXT(D2-A2,"h:mm"))
NOTE: there is no error in the above formula for what it does. What is does not do though is include the "off-work hours" of 4pm-7am, M-F. In the table provided below, the answer is '0 days, 20 hours, 48 minutes". For the purposes of what I need (to exclude the time between 4pm and 7am) it needs to be '0 days, 5 hours, 48 minutes".
Any help you can provide is greatly appreciated! Note: I would upload the file, but am not finding where I can do that (I think because I am new to this forum).
Elapsed Work Time Table
