Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Date/Time format for cumulative hours beyond 24? - Visokio Forums
Formulae: Date/Time format for cumulative hours beyond 24?
  • aozols November 10, 2014 3:49PM
    I'm pulling in Google Analytics data and attempting to create a formula to calculate average session duration (total session duration/sessions).

    The issue that I'm running into is when summing my "total session duration" date field. The HH:mm:ss format works up until the hour parameter hits 24 (as the setting for HH is a 24-hour clock). Is there a way to get around this 24-hour restriction? Should I be using a different format?

    Example attached.

    image
    Attachments
    session duration.JPG 35K
  • 4 Comments
  •        daniel November 10, 2014 7:47PM
    I suggest splitting that time field into 3 separate columns for hours, mins and seconds (do this by expand value operation on the ":")
    then creating a new field that is the summation of the time in just seconds where it is:

    [Total Time (s)] = [Seconds] + 60*[Minutes] + 3600*[Hours]

    The total time in this case would be an integer and not a date/time.

    Then for average session duration it would just be [Total Time (s)]/[Sessions]
    To get minutes just divide that by 60 and for hours by 3600.


    If you want to do it in just one single field the formula* is a bit long but would look like:

    [Total Time (s)] =

    numvalue(right([Session Duration],2))
    +
    60*numvalue(left(right([Session Duration],5),2))
    +
    3600*numvalue(left([Session Duration],(len(Session Duration)-6)))

    Then divide that by [Sessions] and 60 for mins and 3600 for hours.

    Most likely your session durations would be in the seconds or minutes at most, not hours.

    *Just an addendum for the second formula, if you use left or right functions you have to make sure your time/session duration field is always defaulted to text. The numvalue() will change the individual subtext into a numerical format for your output.
  • aozols November 10, 2014 8:17PM
    Thanks for the suggestion.
  • aozols November 11, 2014 4:18PM
    Once I get to seconds, is there a formula that can convert this back to the minutes:seconds format?

    For example:
    Rather than 80 seconds showing 1.34 minutes (80/60), having it output 1:20 (1 minute plus the 20 seconds as a remainder)?
  •        daniel November 11, 2014 4:47PM
    This should give a text output format in terms of minutes:seconds, just create a new field where duration is the time duration in seconds of the previous formula.

    IF(
    [Duration]>60, intfloor([Duration]/60)+":"+([Duration] - (intfloor([Duration]/60))*60),
    [Duration]
    )

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership