# Google sheets formula to calculate hours elapsed between two datetime stamps & exclude weekends

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

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

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