Duration of Time

I need to calculate the time between start and end for volunteers and missing person search tracking. I have a column for “In Time” and “Out Time” both being Date/Time. I try to create a formula to calculate it but it just puts a date and time not hours.

I need something like:

In Time: 6:30
Out Time: 7:45
Duration: 1.75

1 Like

@Damon_Talbot - Welcome to the community!

I think you stumbled upon a limitation - kinda. You can do it today using a workaround.

  1. Use a column formula to calculate the difference
  2. Use a second column - make its type Duration - and make each cell’s value the same as the Col Formula cell in the same row.

Also two things:

  1. The formula result of the difference of 2 date-times ( or dates) defaults to the number type. In the Column Formula settings, we need to add ‘Duration’ to the list of formatting types. Tracking this as : #004396

  1. We need to add Time Datatype support (already part of our roadmap). That would enable users to enter 7:30 am , 9:20pm etc.
1 Like

The Column Formula could also be (G-F)*24 to return the hours as a number (instead of using the Duration column). Using the Duration is currently the only way I can find to display the time difference in the format “[h]:mm” and the TEXT function does not allow this standard format code.

Note the following from Excel:

In spreadsheet.com, if you try to use =TEXT(1.08333,"[h]:mm") or =TEXT(1.08333,"[mm]") the TEXT function ignores the [h] and [mm] format codes rather than returning the correct value or an error (see below):

TEXT(1.08333,“h:[mm]”) and TEXT(1.083,"[h]:[mm]") should return an error. Similar thing applies to duration in seconds using “[ss]”

1 Like

@Vertex42 - Thanks for the feedback. TEXT function improvements are planned for Time, Duration, { Fraction and Scientific - lower priority } types in the near - medium term.

2 Likes

Duration is now available as a Format option in Column Formulas.

4 Likes

This is great (duration as a column formula formatting option).

I’m still hoping for the “7:30 AM” time format option for timesheet compatibility. All of my timesheets include the date in a separate column from the times (because entering “7:30 AM” is easier/faster than also entering the date, and people are often/usually filling out timesheets based on the past rather than being able to use the “Now” shortcut). I know this is a redundant request. :slight_smile:

2 Likes

Agree, and we are planning to add an additional “Time” data type.