Calculate Turn around time - working all 7 days a week with shift time excluding holiday list

[surevyas]

Hello Team
I am preparing TAT report (Revert time) for one of the country which works all the 7 days.
I have checked the previous post however those were excluding weekends and I didn't got satisfactory answer.

I have attached the report, also tried adding the formulae, however for some reason it is not calculating time accurately. also please help to eliminate same issue as well which is shared below in 1st file.
Can someone please help and assist on this topic.
Attached file: TAT - Dubai Exp - Copy for reference.

Also, there is another file, where country works for 6 days, however in some cases it Time exceeds the target it is not showing correct time. row no 30 filtered in file for reference. and example below.
Attached file : TAT - SG_MY Exp-Revised Copy

User	Date	Bl No	Bkg No.	SGSIN	Invoice Generated	Invoice Sent	Received Date & Time	Sent Time	Tat (In Hours)
Rahul Borsutkar	19-01-2023	VASSINCOK015849	VASSIN2300076	SGSIN	Yes	No	16-01-2023 14:36	19-01-2023 09:37	1.01.00

 

[vletm]

surevyas​

You missed to explain one month ago - what is TAT?
Are somewhere Your expected verified results?

 

[surevyas]

TAT - is Turn around time. i.e. time difference between when the email was received and when the email is reverted. and agreed revert time
If the actual time difference is higher than agreed time then TAT (Turn Around Time) Not met else met.
And to Evaluate actual Revert time. we have conditions that the time calculated should calculate only received and sent between working hours and should exclude holiday. Anything outside working hours should not calculate

Hope I am able to answer your question.

Regards
Suresh Vyas

 

[vletm]

surevyas​

I asked two questions.
Did You check that You are using same terms with Your files?

 

[surevyas]

Format is same, terms are same however, there is minor change in when making formula for 7 days i.e.
Instead of "1111101" I have taken "0000000" so that all the 7 days are captured.

If you have an alternate solution which can suffice the the purpose of 7 days then please assist.

 

[vletm]

surevyas​

I've waited for Your answers to my questions ...
without clear answers
I have to guess 'some' parts.
Press [ Do It ]-button to refresh results.
You could see right side some values which should help You.

 

[surevyas]

Sorry was out of the station, so I could not respond. I have checked the file, however, it is showing an error.

I am keen to look at solutions using Excel formulas rather than Macro.
I will explain in short, an overview what i am looking.

Team work 7 days a week. Mon to Fri 7am to 5PM and Sat and Sun 7AM to 12PM. Any job processed with shift time should calculate the time else it should not calculate the time. Also there are country holiday. if day falls under holiday then time should not calcuate the time

 

[vletm]

surevyas​

Your I have checked the file, however, it is showing an error.
Where do it show error? For me - none.

For someone else who'll offer a formula solution:
Could You give some new information?

 

[surevyas]

Sorry for the delayed response, It was saved in my downloads file and then I saved it on my desktop. and clicked on properties and unblocked it. post which the macro ran.
I tried clicking on Do it. Macro has run however was not able to relate.

If you can please help me in creating the formula, would be a great help.
Let me know if I should provide you the fields again without formula

Regards,
Suresh Vyas

 

[vletm]

surevyas​

... Ten days

Did You write above that did not show error
... or what did You try to explain?

What was not able to relate?
> Based above, I cannot do any modifications.

Why/where do You need that with formulas?
... some formulas could be ... long ... long and long.

 

[surevyas]

Dear vletm
Thank you for prompt reply, and apologies for confusion.
if you check my intial workbook which i had shared please refer "TAT Dubai Exp-Copy" file.
In Column J, I had tried to update the formula, however didnt gave me correct output.
the scenario i had already explained.

7 days a week which their shift time. so time should calculate with in working hours only and exclude holiday list date

 

[vletm]

surevyas​

I've used Your the 1st file.
Oct 29, 2023 - I offered one solution.
If I remember, same file could use with Your 2nd file too; after settings.

Do You mean that now Your 1st file is workings? ... or what?
You seems to skipped again all my questions again ... for some reason?
It's a challenge to even try to help without proper answers.

About outputs - there are more possibilities to get incorrect output than correct output.
Your the 1st file has ... many ... I could write a lot of rows of data
... with something like my solution - You do not need to take care any formulas.

 

[surevyas]

Dear Vletm
I have checked the logic in the solution that you have provided during weekend and it fits my requirement
Thank you so much for your help.
In case any query or support needed, will reach out.

Once again Thanks and Apologies
Regards
Suresh Vyas