Time Formula

Ken Hoffman

I have a client who I need to track if we met their SLA for service each month. We have a 4hr response time from the time the call comes in to first respond. We then have to resolve the issue in 48hr. Our system spits out dates and times of when the calls come in and when we completed them. Is there a formula for for a Yes/No if we met the SLA in 4hr and completed in 48hr?

If the time and date have to be in separate columns that is fine I can easily modify that/

Thanks in Advance.

Ken

ericncarr

I don't have the bandwidth to write all the formulas but this is how I would approach it:

1. Separate the date and time into two columns as you mentioned being able to do
2. Separate hours from minutes using a parsing formula
3. If possible, put time in 24 hours instead of AM/PM - if this isn't possible, preserve the PM (probably with an IF statement honestly) in your separation of hours and minutes. I'd also build a conversion table in another sheet that I would use as a reference in a "24 hour conversion" column on your main sheet for each of the three time stamps you have if the time has "PM" in it (=if(contains("PM", Time@row), index(collect(reference, reference, =Time@row) - reference table would be 2 columns, first column PM time, 2nd column corresponding 24 hour time - ex 1PM, 13)
4. Convert your minutes to decimals by dividing them by 60. So for 3:30 you'd have 1 column with 13 and another column that is 30/60 = .5
5. Finally, recombine the 24-hour value and the decimal value in another column so that for instance 1:30:00 PM is actually 13.5
6. The 4 hour SLA column would have to do an if statement to check if the first on site date and the requested date are the same. If they are, it would calculate First On Site Time - Request Time. if the number is more than 4, it gets flagged or does whatever you need it to do if the SLA was missed. If the dates are different (call was received at 11PM and first on site was 2AM the next morning), you'll need to do a formula that adds together the hours from the first response column with (24 - hours in the call time column). In the 11PM, 2PM example it would be 2 + (24-23) = 3 hours.
7. For the 48 hour SLA you would do an if statement to see if the difference between the resolve and request dates is less than 2, if so you met SLA. If it's more than 2, you missed SLA. If it's exactly 2, then you would compare the request time and the resolve time. If the resolve time is greater than the request time (ex 14.5 vs. 9.1666, in other words 2:30PM vs. 9:10AM), then SLA was missed. If the resolve time is less than the request time, you met SLA (ex 8.5 vs 15.1666). You could also calculate how much SLA was missed by or how much time you exceeded SLA by using the data you have.

ericncarr

Good morning,

Try parsing out the date and then converting it in the RD date column like they do in this post:

https://community.smartsheet.com/discussion/82169/how-to-convert-a-text-string-to-a-date

To check the 48-hour SLA this is the formula that I've come up with:

=IF([CD Date]@row - [RD Date]@row < 2, "Yes", IF(AND([CD Date]@row - [RD Date]@row = 2, [CD 24hr Dec]@row <= [RD 24hr Dec]@row), "Yes", "No"))

To check the 4-hour response time SLA:

=IF(AND([RD Date]@row = [FoS Date]@row, [FoS 24hr Dec]@row - [RD 24hr Dec]@row <= 4), "Yes", "No")

Amit Wadhwani

Hi @Ken Hoffman

You can create 2 helper columns which will have the date and time in separate columns – Create on Date column and One Time column.

Apply the formula in the date column

=DATEONLY([Created Date]@row)

Time column

=RIGHT([Created Date]@row, 7)

I hope this works for you 

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Did my solution help you? Do not forget to hit the awesome icon.

ericncarr

I don't have the bandwidth to write all the formulas but this is how I would approach it:

1. Separate the date and time into two columns as you mentioned being able to do
2. Separate hours from minutes using a parsing formula
3. If possible, put time in 24 hours instead of AM/PM - if this isn't possible, preserve the PM (probably with an IF statement honestly) in your separation of hours and minutes. I'd also build a conversion table in another sheet that I would use as a reference in a "24 hour conversion" column on your main sheet for each of the three time stamps you have if the time has "PM" in it (=if(contains("PM", Time@row), index(collect(reference, reference, =Time@row) - reference table would be 2 columns, first column PM time, 2nd column corresponding 24 hour time - ex 1PM, 13)
4. Convert your minutes to decimals by dividing them by 60. So for 3:30 you'd have 1 column with 13 and another column that is 30/60 = .5
5. Finally, recombine the 24-hour value and the decimal value in another column so that for instance 1:30:00 PM is actually 13.5
6. The 4 hour SLA column would have to do an if statement to check if the first on site date and the requested date are the same. If they are, it would calculate First On Site Time - Request Time. if the number is more than 4, it gets flagged or does whatever you need it to do if the SLA was missed. If the dates are different (call was received at 11PM and first on site was 2AM the next morning), you'll need to do a formula that adds together the hours from the first response column with (24 - hours in the call time column). In the 11PM, 2PM example it would be 2 + (24-23) = 3 hours.
7. For the 48 hour SLA you would do an if statement to see if the difference between the resolve and request dates is less than 2, if so you met SLA. If it's more than 2, you missed SLA. If it's exactly 2, then you would compare the request time and the resolve time. If the resolve time is greater than the request time (ex 14.5 vs. 9.1666, in other words 2:30PM vs. 9:10AM), then SLA was missed. If the resolve time is less than the request time, you met SLA (ex 8.5 vs 15.1666). You could also calculate how much SLA was missed by or how much time you exceeded SLA by using the data you have.

Ken Hoffman

Ken Hoffman

Hi @Amit Wadhwani,

Neither of those formulas worked. For the Dateonly it came up Invalid Data Type and for the time it came up unparseable.

Hi @ericncarr I was able to create a few helper columns after I found this Convert Date and Time to 24 Hour — Smartsheet Community. I then created another column to convert the min to decimal. but when i go to put them back together I get a "0." in my time. Hoe do I fix that?

Here is my Min formula

=IFERROR(MID([Requested Date]@row, FIND(":", [Requested Date]@row) + 1, 2), "")

Here is my Decimal formula

=VALUE([RD Min]@row) / 60

Here is my 24hr with decimal formula

=IF([RD AMPM]@row = "AM", [RD Hour]@row + ":" + [RD Min Deci]@row, "" + IF([RD AMPM]@row = "PM", VALUE([RD Hour]@row) + 12 + ":" + [RD Min Deci]@row, ""))

ericncarr

Hi Ken - the 24 decimal column doesn't need to be in a time format since you'll be using it for calculations, it needs to be a number. As that's the case, just add the RD Hour and RD Min Deci columns to get 11.75 and 17.0333.

I like how you're converting to 24 hours, that makes sense too...here's a formula you can use for that to account for 12AM and 12PM. Basically, if it's AM but not 12, add the hour and decimal, if it's PM and not 12, add 12 to the hour and add the decimal. If it's AM and 12, do 0 plus decimal, if it's 12 and PM just do 12 plus decimal.

=IF(AND([RD AMPM]@row = "AM", [RD Hour]@row <> 12), [RD Hour]@row + [RD Min Deci]@row, IF(AND([RD AMPM]@row = "PM", [RD Hour]@row <> 12), ([RD Hour]@row + 12) + [RD Min Deci]@row, IF(AND([RD AMPM]@row = "AM", [RD Hour]@row = 12), 0 + [RD Min Deci]@row, 12 + [RD Min Deci]@row)))

Then you can compare the different dates as I suggested in my first comment and depending on the outcome calculate the difference between the two times converted to 24 hour decimal. If you need a hand with that let me know, I've been interested in crafting something like this for a while but haven't had an actual use case at my org yet.

Ken Hoffman

Thanks @ericncarr,

That totally makes sense. I tried your formula but something is off. On Row 3 I have a 12 PM but in the conversion it did not add the 0.13. In rows 1 and 2 it added extra numbers. Let me know if it is easier for me to share a sheet with you. I am really new at formulas so it is taking me a lot longer to figure this out. I definitely appreciate

your help. :)

ericncarr

That works fine for me, you can share it with ericncarr@gmail.com and I'll take a look around.

ericncarr

Aha, I got it corrected, I didn't realize it would combine the data that way. I had to insert value() into the formula to convert the parsed text into a number:

=IF(AND([RD AMPM]@row = "AM", [RD Hour]@row <> 12), VALUE([RD Hour]@row) + [RD Min Deci]@row, IF(AND([RD AMPM]@row = "PM", [RD Hour]@row <> 12), (VALUE([RD Hour]@row) + 12) + [RD Min Deci]@row, IF(AND([RD AMPM]@row = "AM", [RD Hour]@row = 12), 0 + [RD Min Deci]@row, 12 + [RD Min Deci]@row)))

Ken Hoffman

Thanks Eric,

Do you know why I am getting a #invalid data type when I use the DATEONLY

=DATEONLY([Requested Date]@row)

Ken Hoffman

Hi Eric,

I still cannot figure out why the DATEONLY formula is not working so I copy and pasted the Dates and turned them into a date column for now.

I added another cheater column for SLA Due Date to add 2 days to the Requested date. Then I was able to put in a Yes/ No formula if we met the SLA with in 2 days. I am still unsure on how to do this if the SLA is met with in 48hr but on a different day.

I also was able to add a formula for the Met 4Hr SLA. Which gave me a number. Ans as you said if the number falls <= 4 within the same day I would meet the SLA. I am also unsure on this one as well on how to do the SLA formula for meeting SLA within the 4 hr but after Midnight.

What I'm looking for is a Yes/ No in the SLA Columns if I met the SLA.

ericncarr

Good morning,

Try parsing out the date and then converting it in the RD date column like they do in this post:

https://community.smartsheet.com/discussion/82169/how-to-convert-a-text-string-to-a-date

To check the 48-hour SLA this is the formula that I've come up with:

=IF([CD Date]@row - [RD Date]@row < 2, "Yes", IF(AND([CD Date]@row - [RD Date]@row = 2, [CD 24hr Dec]@row <= [RD 24hr Dec]@row), "Yes", "No"))

To check the 4-hour response time SLA:

=IF(AND([RD Date]@row = [FoS Date]@row, [FoS 24hr Dec]@row - [RD 24hr Dec]@row <= 4), "Yes", "No")

Ken Hoffman

Thanks for all your help Eric! Much appreciated.

ericncarr

You're welcome @Ken Hoffman !

Ken Hoffman

Hi @ericncarr,

Could you help me with 1 thing?

I need my 48hr SLA YES/NO to not include weekends. How would I modify this?

To check the 48-hour SLA this is the formula that I've come up with:

=IF([CD Date]@row - [RD Date]@row < 2, "Yes", IF(AND([CD Date]@row - [RD Date]@row = 2, [CD 24hr Dec]@row <= [RD 24hr Dec]@row), "Yes", "No"))

ericncarr

Hi @Ken Hoffman been away for a bit - did you already solve for the weekend part? I think there's a few ways to solve for that but would have to dive in a bit.