Calculating Durations

Eid E. Eid

I have columns in my sheet with "Departure TIme" and "Arrival Time" in the format hh:mm

Since there is no "Time" Type for the columns, so I had to kep the Column Type as Number/Text

I am looking for a way to substract these 2 columns to obtain the duration of the trip.

Many thanks for any help

Find more posts tagged with

Human Resources

Sheets

Formulas

Comments

SoniaR

Hi Eid E. Eid -

Currently, there isn't a way to use formulas with values formatted as hh:mm (as the : will make the value a text string). Our Product team is looking in to building a time formatted column type and I've just added your vote to this category on the request list.

Thanks,

Sonia

Victor van R

@SoniaR please add my vote as well. This new functionality is much appreciated

Joel Johnson

Put my vote on this too.

I have done a work around for simple duration calculations, and posted it in the below discussion:

https://community.smartsheet.com/discussion/time-calculation

Patrick Lawler

Eid and group,

This exact problem can be solved using Azuqua, an integration partner of Smartsheet. I work for Azuqua and the logging timestamp in a column based on a checkbox being marked (checked) is one of the many scenarios that we enable inside of Smartsheet.

Using Azuqua you can set up a monitor for a change to column and in your particular case check to make sure the checkbox has been marked and then confirming the checkbox has been marked a timestamp / date will be entered into the column you specify.

For your use case you would set up a checkbox for both departure and arrival. When you mark those checkboxes Azuqua will log a timestamp for each of those actions into the columns you specify and then using a formula you get the exact duration.

If you want to learn more please visit our app listing at Smartsheet here:

https://www.smartsheet.com/apps/azuqua

Please feel free to follow up with any questions.

-Patrick