Forum Discussion

lamysroe's avatar
lamysroe
Icon for Advocate I rankAdvocate I
7 years ago
Solved

Time format hh:mm adding an hour

I have columns that were orignally in milleseconds and were calculated into decimal hours. From there I used the following to get the duration into hh:mm format. It worked for half of the columns, but for the other half I am over by an hour. Based on the examples below, I think this is happening because the minutes are past the half hour, but the minutes are correct on all entries. 

 

19.64 is coming out to 20:38 - off by an hour

2.71   is coming out to 3:42   - off by an hour

8.04   is coming out to 8:02   - Correct

3.37   is coming out to 3:22   - Correct 

 

 

1st response bhrs hh:mm = 
var hourNo=INT([Avg First Response Time Bhrs Calculated]/60)
var minuteNO=MOD([Avg First Response Time Bhrs Calculated],60)
var secondNo=INT(([Avg First Response Time Bhrs Calculated]-INT([Avg First Response Time Bhrs Calculated]))*60)
return
FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")
Need Help
  • Icey's avatar
    Icey
    7 years ago

    Hi lamysroe ,

    You can change your Column like this:

    1st response bhrs hh:mm 2 =
VAR hourNo =
    INT ( [Avg First Response Time Bhrs Calculated] / 60 )
VAR minuteNO =
    INT ( MOD ( [Avg First Response Time Bhrs Calculated], 60 ) )
VAR secondNo =
    INT (
        (
            [Avg First Response Time Bhrs Calculated]
                - INT ( [Avg First Response Time Bhrs Calculated] )
        ) * 60
    )
RETURN
    FORMAT ( hourNo, "#00" ) & ":"
        & FORMAT ( minuteNO, "#00" ) & ":"
        & FORMAT ( secondNo, "#00" )

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi lamysroe ,

    You can change your Column like this:

    1st response bhrs hh:mm 2 =
VAR hourNo =
    INT ( [Avg First Response Time Bhrs Calculated] / 60 )
VAR minuteNO =
    INT ( MOD ( [Avg First Response Time Bhrs Calculated], 60 ) )
VAR secondNo =
    INT (
        (
            [Avg First Response Time Bhrs Calculated]
                - INT ( [Avg First Response Time Bhrs Calculated] )
        ) * 60
    )
RETURN
    FORMAT ( hourNo, "#00" ) & ":"
        & FORMAT ( minuteNO, "#00" ) & ":"
        & FORMAT ( secondNo, "#00" )

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.