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_st...
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.
     
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.
     
    • laurent_dec's avatar
      laurent_dec
      Frequent Visitor
      3 years ago

      Hi grantsamborn , thank you so much, works perfectly and super simple. You don't wanna know what complex formulas I have tried :). 🙏🏻