How to represent the number of seconds as hours, minutes and seconds

Sigma Formulas and Functions
#1

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

image

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

1 Like
#2

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.

1 Like
#3
#4
#5
#6
#7
#8
#9
#10
#11
#12
#13
#14
#15
#16
#17
#18
#19

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):

  1. Calculate the duration in seconds, e.g. [Seconds from Date to Today] = DateDiff("second", [Date], Today())

  2. Create 3 new columns, called [Hours], [Minutes], and [Seconds] using the following 3 formulas:
    [Hours] = Div([Seconds from Date to Today], 3600)
    [Minutes] = Div([Seconds from Date to Today] - [hours] * 3600, 60)
    [Seconds] = [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).