Hello,

I am trying to calculate total work hours between 2 dates. I need to exclude 2 coffee breaks and a lunch break as well as weekends and holidays.

Start Date & Time: 15/09/2017 2:13:00 PM
End Date & Time: 18/09/2017 3:57:15 PM

1st Break: 9:45 AM to 10:00 AM
Lunch Break: 12:00 pm to 12:30 PM
2nd Break: 2:30 PM to 2:45 PM
Business Hours: 08:00 AM to 4:30 PM
Holidays: (A list of Holidays are written in a separate sheet)

Weekdays : Monday to Friday
Weekends: Saturday & Sunday

If I calculate the total work hours for the time periods it should give me 8 hour 59 minutes and 15 seconds, because business hours are closing by 4:30 PM on Sept 15 and it is Friday. So it should not include the time from Sept 15 4:30 pm until Sept 18 8:00 AM since there is no work on Saturday & Sunday.

So far I have this formula:
=(NETWORKDAYS(F2,G2, 'Holiday List'!$A$2:$C$26)-1)*("16:30"-"8:00")+IF(NETWORKDAYS(G2,G2, 'Holiday List'!$A$2:$C$26),MEDIAN(MOD(G2,1),"16:30","8:00"),"16:30")-MEDIAN(NETWORKDAYS(F2,F2, 'Holiday List'!$A$2:$C$26)*MOD(F2,1),"16:30","8:00")

Column F - Start Date and Time
Column G - End Date and Time
Holiday List - A to C array is the list of Holidays.

This is working but does not include the BREAKS and I don't know how to add the breaks into the formula.

Any help would be much appreciated!

Thank you