Forum Discussion

maga30's avatar
maga30
Copper Contributor
Jan 20, 2023

How convert column with number to hours

hello all I really need your help, how can I change the format of these numbers to be able to read them in hours and minutes example (with sharepoint list) 3:45  
SvenSieverding's avatar
SvenSieverding
Bronze Contributor
Jan 21, 2023

HI maga30 

 

Excel calculates that number (i.e. 0.15625 for the timestamp 3:45) through this formula

0,15625 = ((3*3600)+(45*60)) * (1/24/60/60)


You can re-format that number to a date format "h:mm", you apply this column formatting

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=(floor((Number(@currentField)/Number(1/24/60/60))/3600))+':'+padStart(toString((floor(((Number(@currentField)/Number(1/24/60/60))-(floor((Number(@currentField)/Number(1/24/60/60))/3600)*3600))/60))),2,'0')"
}



Best Regards,
Sven

maga30's avatar
maga30
Copper Contributor
Jan 23, 2023

SvenSieverding 

Thanks a lot! works! really good. 

The only problem the hours should be for example 8:00 and not 7:59. 

 

Could you please help me with that? 

 

 

  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    Jan 23, 2023

    Hi maga30 ,

     

    sure.... can you give me the original number for 07:59 and what the date should be (Not as a Screenshot, so I can copy&paste)

    Best Regards,
    Sven

Resources