1

What is the best formula to calculate the hh:mm:ss lapsed between end datetime and start datetime and also exclude weekends? I know I can simply subtract end and start datetime ( End Datetime - Start DateTime) but I don't know how to exclude weekends from the formulas.

Start DateTime End DateTime Response Time in hh:mm:ss
29 Oct 2021 6:56:14 30 Oct 2021 8:13:11
22 Oct 2021 16:26:04 25 Oct 2021 20:08:24
14 Oct 2021 2:36:34 20 Oct 2021 18:20:58
20 Oct 2021 6:56:14 20 Oct 2021 8:13:11

1 Answer 1

1

Use DAYS to calculate the total number of days between two days, and NETWORKDAYS.INTL to calculate the number of days excluding weekends. The difference is the number of days (net of weekends). Deduct this from the number of days from the simple formula End Datetime - Start DateTime)

Use this formula:

=(B3-A3)-((if(days(B3,A3)-NETWORKDAYS.INTL(A3,B3,1)<0,0,days(B3,A3)-NETWORKDAYS.INTL(A3,B3,1)))*1)


Sample

snapshot


Logic

  • (B3-A3) - calculate raw duration
  • days(B3,A3) - calculate number of days difference
  • NETWORKDAYS.INTL(A3,B3,1) - calculate days difference excluding weekends
  • if(days(B3,A3)-NETWORKDAYS.INTL(A3,B3,1)<0,0,days(B3,A3)-NETWORKDAYS.INTL(A3,B3,1)) - calculate the difference
  • if... * 1 - calculate duration
  • (B3-A3) - if... * 1 - deduct weekends from raw duration

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.