Calculating Difference between start time and end time

Joanna Claxton

Hello. I realize there are already several threads on calculating time. I've reviewed the ones I've found and seem to be missing something basic in th setup of my grid sheet….

Here is what I'd like to do:

I am creating a sheet that will be tracking: load times, delivery times, and travel times for our drivers. Ideally I'd like to set it up to have "load time in", "load time out", "delivery time in", and "delivery time out" columns. I'd like a formula column to calculate the difference between load time out and load time in, a column to calculate the difference between delivery time in and delivery time out, and a column to calculate the travel time to and from (so, load time out to delivery time in, delivery time in to load time out)

The formulas I've tried either return invalid data, invalid data type, or unparsable, so I'm thinking I might be missing a basic step that designates the data in the column as time?

It kind of surprises me that Smartsheet doesn't just have a Time column type.

jmyzk_cloudsmart_jp

Hi @Joanna Claxton

Since there is no Smartsheet function to calculate time, we usually use the TEXT function to get the Day, Hour, and Minute from the time format text.

The most typical time format is the right part of the [Created] column.

In the demo sheet below, I first get the [Date from Created Text] or equivalent to cope with the time zone issue.

Time zones and system columns
System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones#:~:text=When%20you%20set%20up%20your,Settings%20%3E%20Settings%3E%20Time%20Zone.

[Date from Created Text] =VALUE(MID(Created@row, FIND("/", Created@row) + 1, 2))

Then, to simplify the TEXT-based formula, I use the TIME function to convert the time part into the 24-hour format without worrying about AM or PM. (The 1 option in the TIME function gives you the 24-hour formatted time)

[Time in 24 format] =TIME(MID(Created@row, FIND(" ", Created@row) + 1, LEN(Created@row) - FIND(" ", Created@row)), 1)

https://app.smartsheet.com/b/publish?EQBCT=0b48f4b4e98641aa9b804b0b0b69d922

You can convert the time part to the value of minutes. For example, 13:48 can be converted by 13*60+48 = 828.

If you get the Date and Minutes value, the [In to Out] minutes value is as follows:

( [Date of Out]-[Date of In] ) *60 *24 + [Minutes of Out] - [Minutes of IN]

The actual formulas are as follows;

[Minutes] =VALUE(LEFT([Time in 24 format]@row, 2)) * 60 + VALUE(RIGHT([Time in 24 format]@row, 2))
[In to Out] =IF(ISNUMBER([Matching Row Number]@row), ([Date from Created Text]@row - INDEX([Date from Created Text]:[Date from Created Text], [Matching Row Number]@row)) * 60 * 24 + Minutes@row - INDEX(Minutes:Minutes, [Matching Row Number]@row))
[Matching Row Number] =IF([load time]@row = "load time out", INDEX(COLLECT([Row Number]:[Row Number], [Primary Column]:[Primary Column], [Primary Column]@row, [load time]:[load time], "load time in"), 1))
[Done] =IF(OR(ISNUMBER([Matching Row Number]@row), HAS([Matching Row Number]:[Matching Row Number], [Row Number]@row)), 1)

Note the [Matching Row Number] gets to the corresponding [load time in] row number to the [load time out] row. Then, we can use the row number in the INDEX formula to designate the row number.

If you have time-formatted columns in a row, as in your example, the formula becomes simpler. You just need to convert the Date and Time values to minute values and get the differences.