TAT Calculation

[pramodagroiya]

Hi,

I have excel sheet, wherein we have ticket raise date and time & ticket close date and time. i need to calculate the time difference if the ticket is closed with 4 hrs to TAT or not. also if the ticket is created after 2:30 PM then the time calculation will be till 6:30 for TAT and rest of the TAT will start next day after 9:30 AM. need to exclude saturday and sunday.

sample file is like this:

Ticket Number Date Help Topic Source Current Status Last Updated
158635 22-02-2016 09:32 Featured Static Listing Phone Closed 22-02-2016 12:32

[joris moerings]

Hi, see if this formula does the trick in calculating the TAT:
=(NETWORKDAYS(B2,F2)-1)*("18:30"-"09:30")+IF(NETWORKDAYS(F2,F2);MEDIAN(MOD(F2,1),"1 8:30";"09:30"),"18:30")-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:30","09:30 ")

The formula to check f TAT is realized is:=IF(G2*24>4,"No","Yes")

[pramodagroiya]

Hi, see if this formula does the trick in calculating the TAT:
=(NETWORKDAYS(B2,F2)-1)*("18:30"-"09:30")+IF(NETWORKDAYS(F2,F2);MEDIAN(MOD(F2,1),"1 8:30";"09:30"),"18:30")-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:30","09:30 ")

The formula to check f TAT is realized is:=IF(G2*24>4,"No","Yes")

Hi Joris,

thanks for help but this formulla is not working, giving #Value error.

Regards,
Pramod Kumar

[Excel Fox]

Pramod, are you saying that any ticket that is created before 6:30PM should have a TAT of 4 hours. And if any ticket is created after 6:30PM, then the TAT should be 4 hours from 9:30AM the next working day? And you need a formula to check whether that TAT is met or not?

[Excel Fox]

If what I asked is correct, then you could validate the TAT compliance using this formula

=IF(MOD(B2,1)>TIME(18,30,0),F2-WORKDAY(B2,1)+TIME(9,30,0),F2-B2)<=TIME(4,0,0)

But if you are looking for something else, let me know.

[pramodagroiya]

If what I asked is correct, then you could validate the TAT compliance using this formula

=IF(MOD(B2,1)>TIME(18,30,0),F2-WORKDAY(B2,1)+TIME(9,30,0),F2-B2)<=TIME(4,0,0)

But if you are looking for something else, let me know.

Hi,

Thanks this was really a great help, i really appreciate your efforts. earlier it took me hours to calculate the data, but now with this i can calculate thousands to rows within a minutes.

thanks once again.

Regards,
Pramod Kumar