Excel Formula to calculate the time difference including Weekends

[Ebraham]

Hi,

I have got a formula which calculates the working hours between the two dates excluding the weekends and holidays. Office working hours are 9.30 to 6.30. Below is the formula.

E2 is the Start Date & Time and M2 is the End.

=(NETWORKDAYS.INTL(E2,M2,1,$AI$2:$AI$4)-1)*("18:30"-"9:30")+IF(NETWORKDAYS.INTL(M2,M2,1,$AI$2:$AI$4),MEDIAN(MOD(M2,1),"9:30","18:30"),"18:30")-MEDIAN(NETWORKDAYS.INTL(E2,E2,1,$AI$2:$AI$4)*MOD(E2,1),"9:30","18:30")

What do i need to fine tune if i want to include the weekends and holidays time as well ?

 

[Taul]

Hi,
I have retained the original format of the formula but removed the holidays and added the Networksdays mask of 7 digits, one for each day of the week.
0 = working day, 1 = non-working day (weekend). It starts on a Monday, so a string of "0000011" would imply a working week with sat & sun set as the weekend. So you have the option to change it in the future.
You could also retain the Holiday range in the formula but simply leave the holiday range blank if you wish.
Leaving all seven digits as zero, means that all days of the week are considered.

=(NETWORKDAYS.INTL(E2,M2,"0000000")-1)*("18:30"-"9:30")+IF(NETWORKDAYS.INTL(M2,M2,"0000000"),MEDIAN(MOD(M2,1),"9:30","18:30"),"18:30")-MEDIAN(NETWORKDAYS.INTL(E2,E2,"0000000")*MOD(E2,1),"9:30","18:30")

also, more info here:-

NETWORKDAYS.INTL function - Microsoft Support

NETWORKDAYS.INTL returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.

support.microsoft.com