Date/Time: re- format durations from seconds to minutes?

JfJf · November 20, 2012 10:04AM

Hi, I am having an issue with the time format. Specially when calculating time spent on the websites, from Google Analytics, I get all the times in seconds when I export the data. I would like to display them in minutes, the problem is that it could be more than a minute, lets say 120 seconds, so when I use the "ss" formatting as an input value and then in the output (as I want it in minutes) I will use "mm".. that is my problem, it is all planned for formatting purposes but not for this type of calculations when it comes to time.

What I am intending to have is to display instead of 150 seconds, it will show up as 02:30, or 00:02:30. I hope it makes sense. Is there a solution for that? I am currently using 2.7 b442.

Reference:
http://www.visokio.com/dates-and-times

tjbate · November 22, 2012 10:54AM

Juan - when you take the difference between two Date/Time values, you do not get a fixed point in time as a result, you get an interval or a duration.

Omniscope does not currently have a "duration" format. The best solution for now is to use fractional numbers (e.g. 1.3 hours) and have a separate Text field for displaying the interval correctly formatted as a duration using a formula.

JfJf · November 27, 2012 6:52AM

Perfect. Thank you Thomas, I have applied that solution. However it would be a good extension for the date formats, specially when analysing online user behaviour.

Thanks again.

paola · December 20, 2012 10:42AM

Please see below file that uses a formula to convert time duration in seconds to re-calculate and display it in HH:mm:ss.SSS format. (SSS represents miliseconds)

IF([Secs]=null,null,
TEXTTODATE(
MOD(INTFLOOR([Secs]/3600), 60)+
":"+
MOD(INTFLOOR([Secs]/60), 60)+
":"+
MOD(INTFLOOR([Secs]), 60)+
"."+
MOD(INTFLOOR([Secs]*1000), 1000),
"HH:mm:ss.SSS"
)
)

JfJf · January 17, 2013 5:01AM

Thank you Paola, it is working.

Welcome!

Categories

Ideas Parade

Tagged