Need help in TAT calculation (Excel 2007)

[avikz]

Dear Members,

I need your help in TAT calculation. A file with dummy data is attached for your reference and the criteria is given below. Can anyone please help?

I am using excel 2007 version.

TAT Criteria:
1. Only Sunday is holiday, no public holidays are followed
2. TAT is 4 hours from received time
3. Final outcome should be TAT Met or TAT NOT MET
4. If a file is received on Sunday, the TAT should start from 9:30:00 next day
5. If a file is received post 18:30 on any workday, the TAT should start from 9:30:00 next day
6. All time mentioned here is in 24 hours format
7. Date format is DD-MM-YYYY

Thank you!

[Bo_Ry]

Not sure if this right, if not please mockup TAT time for each row with an explanation.
F2
=IF(SUM(D2:E2)>B2+IF(WEEKDAY(B2)=1,1+"13:30",C2+"4:00"+(C2>--"14:30")*("15:00")),"TAT NOT MET","TAT MET")

[avikz]

Hello Friend,

Thank you very much for replying. However, the formula is not working correctly under different scenario as required. Can you please look into it?

I have made some changes in the data to show few more scenarios. Let me give you an explanation for the first row.

Received date: 15-06-2019 (Saturday)
File received at: 19:30:00 (the cut off time is 18:30:00 because post that no employees are available)

as per above scenario, the TAT should start from 17-06-2019 (Monday) and 9:30:00 and the file should be completed by 13:30:00. If you see the example in the attached file, it should be TAT MET.

[Pepe Le Mokko]

And what does TAT mean please? You cannot expect everyone to understand technical jargon

[avikz]

And what does TAT mean please? You cannot expect everyone to understand technical jargon

I am sorry! TAT means "Turn around time". It's mainly the time frame given to complete each file.

[Bo_Ry]

Please try at F2
=IF(SUM(D2:E2)>WORKDAY.INTL(B2,--OR(C2>--"14:30",WEEKDAY(B2)=1),11)+IF(OR(C2>--"14:30",WEEKDAY(B2)=1),"13:30",C2+"4:00"),"TAT NOT MET","TAT MET")

[avikz]

Please try at F2
=IF(SUM(D2:E2)>WORKDAY.INTL(B2,--OR(C2>--"14:30",WEEKDAY(B2)=1),11)+IF(OR(C2>--"14:30",WEEKDAY(B2)=1),"13:30",C2+"4:00"),"TAT NOT MET","TAT MET")

HI friend,

Thank again for your reply! However, "WORKDAY.INTL" doesn't work in Excel 2007. It is not returning anything!

[Bo_Ry]

How about
=IF(SUM(D2:E2)>B2+OR(C2>--"14:30",WEEKDAY(B2)=1)+AND(C2>--"14:30",WEEKDAY(B2)=7)+IF(OR(C2>--"14:30",WEEKDAY(B2)=1),"13:30",C2+"4:00"),"TAT NOT MET","TAT MET")

[avikz]

How about
=IF(SUM(D2:E2)>B2+OR(C2>--"14:30",WEEKDAY(B2)=1)+AND(C2>--"14:30",WEEKDAY(B2)=7)+IF(OR(C2>--"14:30",WEEKDAY(B2)=1),"13:30",C2+"4:00"),"TAT NOT MET","TAT MET")

Splendid! Thank you very much! Worked like magic!

[Pepe Le Mokko]

Please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
Use the "Quick reply" instead
Thanks