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.
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:
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
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.
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):
@Vertex42 - Thanks for the feedback. TEXT function improvements are planned for Time, Duration, { Fraction and Scientific - lower priority } types in the near - medium term.
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.