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

 

 

 

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