Discussions

Expand all | Collapse all

Convert Numeric field into Duration

  • 1.  Convert Numeric field into Duration

    Posted 5 days ago
    Edited by Daniel Johnson 5 days ago
    Hi All,

    We're porting in data from an API on phone calls and one of the fields is the length of the call. That information comes to us in seconds. From there, how can I get it to show in MM:SS? It's being sent into a numeric field at the moment because I guess the default for the duration field is hours? Right now I've got a numeric field showing 1777, and I'd like a field that shows 29:37.

    In actuality, I need that field to round down from 00:19, and round up from 00:20, but I figured I'd just right that into an if statement once I was able to get the numbers looking right.

    ------------------------------
    Daniel Johnson
    ------------------------------


  • 2.  RE: Convert Numeric field into Duration

    Posted 5 days ago
    You can create a new Formula Duration field with a formula of Minutes([my import field]).

    If you want to round to the nearest Minute, then you can use the Round formula

    Round(Minutes([my import field]), Minutes(1))

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Convert Numeric field into Duration

    Posted 5 days ago
    Hey Mark, 

    I tried that, and 1777 (seconds) displays as 29:37:00 (HH:MM:SS). I changed the Minutes() formula to Seconds() and that worked. I can't use the rounding formula because I need to be able to specify when to round, instead of just in the exact middle.

    ------------------------------
    Daniel Johnson
    ------------------------------



  • 4.  RE: Convert Numeric field into Duration

    Posted 5 days ago
    Yes, you are  correct to use Seconds, that was my error. 

    As for the rounding, if you are still stuck on that as the famous Kirk Tracy says, "if you can say it we can do it".  So if you can say what the rounding rules are then I can help you with the formula.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Convert Numeric field into Duration

    Posted 5 days ago
    Hey Mark,

    It needs be rounded down for anything ending between 0-19 seconds, and rounded up for anything ending in 20-59 seconds.

    ------------------------------
    Daniel Johnson
    ------------------------------



  • 6.  RE: Convert Numeric field into Duration

    Posted 5 days ago
    try this formula duration field 

    var duration RawDuration = Seconds([my input field]);

    var duration FloorOfDuration = Floor($Raw Duration, Minutes(1));

    var duration ExtraSeconds = $RawDuration - $FloorOfDuration;

    $FloorOfDuration
    +
    If($ExtraSeconds >= Seconds(20),Minutes(1), Minutes(0))

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Convert Numeric field into Duration

    Posted 4 days ago
    That worked! Thanks Mark!

    ------------------------------
    Daniel Johnson
    ------------------------------