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 ?
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 ?