Calculate TAT between working dates excluding Sunday and public holiday (sat is half day)

[surevyas]

Hello Team.
Request you to please help me with the excel formula where in Cell A and B are the (received date and time) and (Sent date and time).
Shift from Mon to Fri is 7AM to 5PM and Sat 8AM to 1.30PM
date format is(25-01-2023 12:00:00 AM)

Please help me with creating the formula

 

[surevyas]

Anything comes before and after shift time mentioned including sat shuld not be considered

 

[pecoflyer]

Post a sample sheet ( no pics please). What is TAT ?

 

[surevyas]

Attached file, for your reference.
I have already condition applied from Monday to Friday, excluding weekend (sat and sun).
Now i want the condition to be added in "New Working File" in column F which also includes Saturday time when calculating TAT.
Another sheet : Public Holidays & work hours has details updated for Mon to Fri and Sat.

Request you to please assist in getting the condition updated for Saturday working.

Appreciate your help.

 

[vletm]

surevyas​

Did You explain somewhere as asked - what is TAT?

 

[mohamed ilyas]

Hi

check is this fine for you?

 

[surevyas]

Perfect result. .. Also, I could see the formula is refined and shorted.
Thank you very much for your help.

Have a pleasant day.

Regards
Suresh

 

[S P P]

mohamed ilyas
Perfect Formula.
I just changed it so that the Zeros don't appear.

 

[surevyas]

Dear Team,
I have got new scenario where we are working all days (incldg weekends) except holiday, can someone plz help me with the scneario how to deal with it.. we can use the same above file as reference

 

[vletm]

surevyas​

One question (scenario) per thread
... and You've still open one more similar thread.
This thread seems to have already solution; that's why - closed.