Need Formula to calculate TAT

[jag_nat]

Hello,

I need to calculate a TAT formula for the below case

P1 -- 1 Hour
P2 -- 2 Hour
P3 -- 4 Hour
P4 -- 8 Hour

Shift Start time : 6:30pm
Shift End Time : 3:30am

Suppose a request comes at 9:30pm and it is Priority 1 (P1) then it should be completed at 9:30pm + 1 hour = 10:30pm. I have given the below formula to execute this

=IF(A2="P1",1,IF(A2="P2",2,IF(A2="P3",4,IF(A2="P4",8,0))))

=B2+(TIME(D2,0,0))

The problem here is if the request is P4 then its 8 hr TAT and the formula will calculate the time as 5:30am. Since the shift time end at 3:30am itself the actual TAT should be the next day 8:30pm.

Please suggest the formula to calculate the TAT which includes the start time and end time and also excludes the weekends and holidays.


Regards
Jack

[Pepe Le Mokko]

If only I knew what a TAT is ? Please do not use words not everyone understands - Thx

[bebo021999]

Duplicate post!

[bebo021999]

I am confusing with:

The problem here is if the request is P4 then its 8 hr TAT and the formula will calculate the time as 5:30am. Since the shift time end at 3:30am itself the actual TAT should be the next day 7:30pm.

5h30am-3h30am = 2h00 should be left for next shift which start at 6h30 pm => end time should be 6h30pm+2 = 8h30pm , why you expect it to be at 7h30pm?

[bebo021999]

Assuming your holiday listed in J1:J5
In C2:

Please Login or Register  to view this content.

[jag_nat]

TAT means Turn Around Time i.e, the time within which we need to complete the request.

Yes bebo021999. you are right. It should be 8:30 pm only. I have edited it now. Thank you.

[jag_nat]

Thank you very much bebo021999. The formula is working for P4 9:46pm, however its not working if we give P1 and 3:00am. Its displaying as 02/23/2014 4:00am whereas it should be displayed as 02/24/2014 7:00m

Priority Received Time Due At Hrs
P1 2/22/14 3:00 AM 2/23/14 4:00 AM 1

Could you please help again.


Regards
Jagadishan N.

[bebo021999]

So there is the case that actual start time is earlier than 6:30 PM.
Now to chance B2 to MAX(B2,INT(B2)+6.5/24)

Please Login or Register  to view this content.

Does it work?

[jag_nat]

The second last step in the formula resulted in an error. This part +WORKDAY.INTL(B2,1,1,$J$1:$J$5)

[bebo021999]

Which time which you 'd chosen and got error? How you think it come from that part?

[jag_nat]

Attached is the sheet with your formula applied and the error.

[bebo021999]

As I said before in #5:

Assuming your holiday listed in J1:J5

so you must adapt to your new range J2:J8

Please Login or Register  to view this content.

Anyway, I am so confused with your list of Holiday and guess it is included with weekends. WEEKDAY.INTL(B2,2,1...) itself include Sat and Sun with parameter 1. So J2:J8 contains holiday only instant Jul-4th, Jan-1st,...

[jag_nat]

I thought to include holidays for the month of Feb alone. Since there is no holiday i included Sat and Sun.

The formula is retrieving a time however its not correct. Its displaying as 02/23/2014 7:30AM whereas it should be displayed as 02/24/2014 7:00PM

[bebo021999]

So try to replace 6.5 with 18.5 in my previous formula:

Please Login or Register  to view this content.

[jag_nat]

I changed "6.5" to "18.5" in the formula and it retrieved me 02/23/2014 7:30PM. Previously it was displaying as 02/23/2014 7:30AM.

It should display actually 02/24/2014 7:30PM. What i need to change in the formula further.

Please advice