Calculating Downtime During Hours of Operation

Hello Puni,

Having a table might be easier - you can still have a table and use a single formula

This formula for H3 effectively uses table values within the formula, i.e.

=(SUMPRODUCT(NETWORKDAYS.INTL(E3,F3,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,7.5,9,7.5,7.5,4})-MEDIAN(0,CHOOSE(WEEKDAY(E3,2),7.5,7.5,7.5,9,7.5,7.5,4),MOD(E3,1)*24-CHOOSE(WEEKDAY(E3,2),9,9,9,9,9,9,10))-MEDIAN(0,CHOOSE(WEEKDAY(F3,2),7.5,7.5,7.5,9,7.5,7.5,4),CHOOSE(WEEKDAY(F3,2),16.5,16.5,16.5,18,16.5,16.5,14)-MOD(F3,1)*24))*60

That gives me a result of 2430 minutes (40.5 hours) because you have 1 hour on Sunday, 7.5. on Monday, Tuesday, Wednesday and Friday, 9 on Thursday and ½ an hour on Saturday = 40.5

That will work for any start or end time, even outside the working hours