How convert column with number to hours

New Contributor

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

maga30_0-1674182802531.png

 

5 Replies

@maga30 What is the unit for current values? Are these values in hours/minutes/seconds/milliseconds?

 

How are you adding these values in SharePoint, manually or using any custom solution?


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

@ganeshsanap Thanks for your reply. 

 

This Number are custom  hr and min

maga30_0-1674259430681.png

 

I Importer the data from excel to Share Point list. 

 

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')"
}


Formatted Date.png
Best Regards,
Sven

@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? 

 

maga30_0-1674457766889.png

 

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