Forum Discussion
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
5 Replies
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,
SvenThanks 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?
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
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
I Importer the data from excel to Share Point list.
