Calculate Turn Around Time and SLA

[Dnyan]

Hi All,

I want to calculate Turn Around time and SLA for making Tracker. So required VBA Code for this, so please help me.

Below is the Details:

Shift Start Time: 08:00
Shift End Time: 17:00

Worktype TAT Start Time Turn Around Time Completed Time SLA
ABC 2 Hours 28-07-2017 08:18 28-07-2017 10:18 28-07-2017 09:25 How Much Time taken to complete this
BCD 4 Hours 28-07-2017 08:18 28-07-2017 12:18 28-07-2017 13:32 How Much Time taken to complete this
CDE 2 Days 28-07-2017 08:18 01-08-2017 12:18 31-07-2017 10:48 How Much Time taken to complete this

Want to Automate Turn Around Time and SLA Coloumns,

Also want to highlight SLA cells if its went out of Service Level.

So please help

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[Dnyan]

Thank you for your help, I have attached my file, so please help me with the same

[Richard Buttrey]

Change B6:B8 so that they contain the number of hours. i.e. 2,4,48

Then in D6 copied down

Please Login or Register  to view this content.

although I don't see how you get 1/8/2017 at 12:18 for D6 and in F6 (for number of hours copied down)

Please Login or Register  to view this content.

[Dnyan]

Thank you very much for your help, however one more condition is there
it should Exclude Weekends (Saturday & Sunday) in Turn Around Time.

[Dnyan]

Can anyone help me for this?

[AliGW]

In D6 copied down:

=C6+IF(ISERROR(FIND("Hours",B6)),LEFT(B6,FIND(" ",B6)-1),LEFT(B6,FIND(" ",B6)-1)/24)

In F6 copied down:

=IF(ISERROR(FIND("Hours",B6)),NETWORKDAYS.INTL(C6,E6,1)&" Days",ROUND((E6-C6)*24,2)&" Hours")

[Dnyan]

Thank you very much for your reply.

However In D8 calculation date is coming as 30/07/2017 however it should be excluding Weekends (Saturday & Sunday) i.e. 01/08/2017. So please check

[AliGW]

In D6 copied down:

=IF(ISERROR(FIND("Hours",B6)),WORKDAY.INTL(C6,LEFT(B6,FIND(" ",B6)-1),"0000011")+HOUR(C6)/24+MINUTE(C6)/1440,C6+LEFT(B6,FIND(" ",B6)-1)/24)

[Richard Buttrey]

Thank you very much for your help, however one more condition is there
it should Exclude Weekends (Saturday & Sunday) in Turn Around Time.

Since you hadn't mentioned excluding weekends that was why I flagged up that there was a discrepancy with your D8 cell.

So change the formula I gave you for D6 to

Please Login or Register  to view this content.

and copy down.

[Dnyan]

Since you hadn't mentioned excluding weekends that was why I flagged up that there was a discrepancy with your D8 cell.

So change the formula I gave you for D6 to

Please Login or Register  to view this content.

and copy down.

Thank you very much Sir, However need calculation without considering Completed time. we want Turn around time before completed the task.

[Dnyan]

In D6 copied down:

=IF(ISERROR(FIND("Hours",B6)),WORKDAY.INTL(C6,LEFT(B6,FIND(" ",B6)-1),"0000011")+HOUR(C6)/24+MINUTE(C6)/1440,C6+LEFT(B6,FIND(" ",B6)-1)/24)

Thank you very much, however Still its coming as 30/07/2017 for 48 Hours. it should be 01/08/2017 (Excluding Saturday & Sunday).

[Dnyan]

In D6 copied down:

=C6+IF(ISERROR(FIND("Hours",B6)),LEFT(B6,FIND(" ",B6)-1),LEFT(B6,FIND(" ",B6)-1)/24)

In F6 copied down:

=IF(ISERROR(FIND("Hours",B6)),NETWORKDAYS.INTL(C6,E6,1)&" Days",ROUND((E6-C6)*24,2)&" Hours")

Thank you,however F6 also need SLA time without Weekends.

[AliGW]

It's working here on your data. Working file attached.

[AliGW]

Thank you,however F6 also need SLA time without Weekends.

Try and work it out from what you have been given. Let us know how you get on.

In any case, how can it? You have a weekend completion date in E8!

[Richard Buttrey]

In D8 try

Please Login or Register  to view this content.

[Dnyan]

Try and work it out from what you have been given. Let us know how you get on.

In any case, how can it? You have a weekend completion date in E8!

Thank you very much AliGW, Its perfect what I was looking for. Thanks a lot again.

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Dnyan]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thank you very much, sorry for again. Again I am facing some issue in Time

in the SLA tab Hours should Calculate only between Shift Time i.e. (08:00 to 17:00)
If Task completed within 24 hours then it should be show in Time Format. In this case Time should be 3 Hours

I have attached Excel file again so Please check attached excel file I have mentioned in file also that how should be calculate it.

[AliGW]

You keep adding requirements - how can any of us have known this? We are not mind-readers! Before I have a look, are there any other requirements you are suddenly going to remember you forgot to tell us at the start?

[AliGW]

Having had a quick look at your workbook, this has now become too complex for me. Maybe someone else will be able to look at it for you. I think you might be right after all that it does need some VBA.

[Dnyan]

Okay, thank you very much for your cooperation, However when I entered my data in the file then only I realize that there is a problem.
Thanks again for your help.

[Richard Buttrey]

Okay, thank you very much for your cooperation, However when I entered my data in the file then only I realize that there is a problem.
Thanks again for your help.

...and what is the nature of that problem? I suspect you are now wanting to modify the previous formulae you've been given to take account of start and end times, although at the moment I have no idea how you might want to reflect work outside those hours.

As a minimum with these things we generally need to see what results you expect, along with notes as to how you calculate them so that we know the end goal.

Experience tells me that the best way of doing this type of thing is to creep up on the solution gradually. So in this case I suggest you identify the formulae that will give you the results you expect assuming the working day hours DON'T apply. Once you have established that and the formula is good for all combinations of dates/times then what I normally do is add helper column(s) that work out what the affect of the daily start/end times should be and then add or subtract the helper column calculation as necessary. I've no idea how you expect to handle start/end times outside the 08:00 - 17:00 day but hopefully you do.

Be particularly careful with start/end times that straddle midnight since deducting the start time from an end time will result in a negative number of hours and you'll need to add a +24 constant to a get the elapsed time.

[Dnyan]

Thank you very much for Help, I got correct formula for this.

Please Login or Register  to view this content.

I Put this formula in D6 Cell and it gives me proper Answer which I was searching for.

Thanks again