Calculate Time Difference exclude non-operating hours and holidays.

Copper Contributor

Operation Hours for Monday to Saturday : 9:30am to 10pm
Operation Hours for Sunday : 9:30am to 6pm

TOT is the total time taken task took during operation hours only excluding Holidays Datessvsdvsd.PNG

Help me Write a excel formula on Column F. The answer shown here is wrong

2 Replies
Since you are having different operating hours , it is advised to mention all the dates from start date to end data in a column . You can map week name using text(A2,"DDD") and mapping hours or can use IF condition to get hours automatically


See the attached workbook. You'll have to allow macros.

The holidays range and standard working hours range have been named Holidays and OperatingHours to avoid having to use their literal addresses in the VBA code.