Let’s say you have a field [Event Duration] that shows duration of some event in seconds, and, instead of displaying it as, say, 5,000 sec, you want to format it in hours/minutes/seconds, as 1 hr 23 min 20 sec
Step 1: Create a date field: DateAdd(“second”, [Event Duration], Date(“1970-01-01”))
Step 2. Set the formatting for this field as a custom format: %H:%M:%S
You can also use DateAdd(“second”, [Event Duration], Date(0)) in Step 1 for the same effect. This might make it clearer what it’s doing, starting the Date value at 0 - which happens to be equivalent to “1970-01-01” due to the way dates are stored as numbers.
I came up with another solution since I wasn’t able to get the answers I wanted using the suggestions above (the above solutions don’t work if the difference between dates is more than one day):
Calculate the duration in seconds, e.g. [Seconds from Date to Today] =
DateDiff("second", [Date], Today())
Create 3 new columns, called [Hours], [Minutes], and [Seconds] using the following 3 formulas:
Div([Seconds from Date to Today], 3600)
Div([Seconds from Date to Today] - [hours] * 3600, 60)
[Seconds from Date to Today] - [hours] * 3600 - [min] * 60
Then, using the following formula, you can define the final [Duration] column:
Concat(Text([Hours Since Date]), ":", Text([Minutes Remainder]), ":", Text([Seconds Remainder]))
Of course, you can hide the underlying columns used to make this formula (if you wish).