0

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

ItsMeDee
  • 1
  • 1
  • Please [edit] the post to include: 1. Can you give some sample data and expected output in tabular form? 2. Please explain what the formula is doing that is in error. – Scott Craner Aug 16 '21 at 15:05
  • @ScottCraner, because I am a new contributor to this site, as I understand it I cannot attach a file or even insert a screenshot. The current formula calculates the total elapsed time, less weekends and holidays. I also need it to exclude the hours after 4:00pm and prior to 7:00am, M-F (work hours are 7am-4pm, M-F). Example: Start time: 6/30/2021 11:23 AM; End Time: 7/1/2021 8:11 AM. The current formula result is 0 days, 20 hours, 48 minutes. I need it to be 0 days, 5 hours, 48 minutes. Sorry I am unable to provide the file, and I hope my description is clear to you. – ItsMeDee Aug 17 '21 at 14:15
  • You can and should create a minimal table that includes the expected output and post it as text in the post. Do not attach a file, most will not download a file from a public site. If you cannot format it correctly, someone with enough rep will come along and do it for you. But you must post the table yourself as text. – Scott Craner Aug 17 '21 at 14:17
  • 1
    @ScottCraner, I appreciate the help and guidance. I edited the original post to include the table (hopefully I did this correctly). – ItsMeDee Aug 17 '21 at 15:39

0 Answers0