Need help with tracking time

ROVEMX

I am barely new to Smartsheet but I can see the potential of helping me with my job duties,

I work as a service dispatcher, We have been running out of Microsoft spreadsheets but I don't know a lot of coding and VBA so if I figured out how to obtain the average time in when the tech arrives on-site versus the time back to work(job complete), the average response time its being obtained from the time we got a service call in minus the time arrived on site. (I added to he formula *24 to convert days to hours but did not work)

so far I got days in, but no hours and I need this for my weekly report.

Does anyone know a possible solution for this problem?

Courtney S.

Hi! It looks like your "Average Back To Running" column type is probably "Duration"? Try changing the column type to the Text/Number column type and then you should be able to add that *24 in your formula to get an hours answer.

Also, you may find this post useful:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

ROVEMX

Change the columns as you suggested and follow the instructions, it comes back as #invalid.

Nic Larsen

Give this one a try. I've grabbed it from another post a while back and use it.

In your Average Back to Running - just leave your formula as it was working but without the * 24.

Add a new column that is a text number

And then use this formula. You'll need to retype it based on your column names. Start, End, and Time Difference is your current formula (without the *24).

=ROUNDDOWN((TIME([Start Time - 12 HR]@row) - TIME([End Time - 12 HR]@row)) * 24) + " Hour(s) and " + ([Time Difference]@row * 24 - INT([Time Difference]@row * 24)) * 60 + " Minutes"

Courtney S.

Hi @ROVEMX if you want to use the *24 you'll need to enclose the initial subtraction formula inside of parentheses, so that the final result is the thing getting multiplied. Eg. = (TIME(A) - Time(B)) * 24