Need help in defining the formula of working hours and excluding non working hours

Dear Team,

My company has working timing of Monday to Friday 10:00 to 19:00 and on Saturday 10:00 to 15:00.

I want a formula which helps me in defining the working hours (as mentioned above Monday to Saturday)and also exclude non working hours(which is Mon to fri 19:00 to 10:00 and for Sat 15:00 to Mon 10:00), Sunday and Holiday list (if any).

Any help would be appreciated a lot.

Thanks,
Manish_Gupta

Hello alan,

Many Many thanks for your reply.

I need this working hours formula for the TAT calculation of how much time a User had spend on one file.

I am attaching a sample sheet which we are using currently. Current formula which we are using (in Column "H") is not able to customize the working hours for Saturday and to add Holiday List.

The Column "H" value is the difference of either Column No "B & G" or "F & G" values depends on the Column "E". If Column "E" is mentioned as "Docs Verified" then Column "H" should calculate the time difference of values between Column "B and G" but if Column "E" is mentioned as "Resent" then Column "H" should calculate the Time TAT from Column "F and G".

So my requirements based on attached sample sheet is:

1. Adjustment of TAT timings in Column “H”. The TAT calculation in Column “H” should be based on working timings of 10:00 AM to 19:00 PM on Monday to Friday and 10:00 AM to 15:00 PM on Saturday and should also exclude non working hours of all working days from Monday to Saturday. (Current formula which we are using (in Column "H") would help you to understand my requirement).

2. Formula should also deduct the Sunday and Other Holidays (mentioned in "Holiday List" sheet) from Column “H” automatically.

Many Thanks
Manish_Gupta