Forum Discussion

laurent_dec's avatar
laurent_dec
Frequent Visitor
3 years ago
Solved

Convert decimals to a time column

Hello,

I already tried a many solutions suggested, but still no success.

I have a column with the values of time trackings, expressed in decimals: example: 0,1667 = 10min
My new column:
xx_starting_time_reformat = FORMAT(TIME(TRUNC(KPI_ODOO_account_analytic_line[xx_starting_time],0),(KPI_ODOO_account_analytic_line[xx_starting_time]-TRUNC(KPI_ODOO_account_analytic_line[xx_starting_time],0))*60,0),"long time")

The error:
An argument of function 'TIME' has the wrong data type or the result is too large or too small.

When I use this formule to convert a certain time (example: 14:30:00); it works perfectly.

Can anyone correct the formule? Thanks!

Need Help
  • grantsamborn's avatar
    grantsamborn
    3 years ago

    Hi laurent_dec 

    Does this help?

     

    xColumn = FORMAT( [xx_starting_time] / 24, "long time" )
     
    Also, look for blank or negative values in the different parts of your column definition as that will raise the error you received.
     

4 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    Hello laurent_dec,

     

    xx_starting_time_reformat = FORMAT(TIME(0,ROUND(KPI_ODOO_account_analytic_line[xx_starting_time]*60,0),0),"long time")

     

    In the above, the ROUND function is used to convert the decimal value to the nearest minute by multiplying the value by 60, rounding to the nearest integer, and then dividing by 60 again to convert it back to a decimal value representing minutes. The TIME function is then used to format the result as a time value.

     

    I hope this helps resolve the issue you were facing. Let me know if you may need any further assistance.

    • laurent_dec's avatar
      laurent_dec
      Frequent Visitor
      3 years ago

      Hello Sahir_Maharaj ,

      Thanks for your feedback.

      The time format for the decimale is like this:
      Another example: 1,1667 = 1 hour and 10 minutes
      So, before the comma are the hours, simple.
      After the comma are the minutes, but on a scale of 100 instead of 60.

      In your formula, don't I need to substract the 1 before the comma?

      Thanks, Laurent.

      • grantsamborn's avatar
        grantsamborn
        Icon for Solution Sage rankSolution Sage
        3 years ago

        Hi laurent_dec 

        Does this help?

         

        xColumn = FORMAT( [xx_starting_time] / 24, "long time" )
         
        Also, look for blank or negative values in the different parts of your column definition as that will raise the error you received.