In this article, I will explain How to
Convert Numbers To Time In SharePoint Calculated Field
I have a SharePoint list, one of its columns is a number column that holds a duration value with minutes.
I would like to display the number of minutes as a Time Format (hh:mm) in the SharePoint Calculated field as shown below:
- Create a SharePoint List.
- Add a new number column for the minute’s value.
- Create a calculated field to convert the minute’s value to time format (hh:mm).
Convert Number field To Time field In SharePoint Calculated Field
The number column settings should be
- The field data type must be a Number.
- The number of decimal places should be 0.
Display Number field as a Time format in Calculated Field Formula
- Go to List Settings.
- Add a new Column.
- Set the column name as you prefer.
- Set the column type to Calculated (calculation based on other columns).
- Download The SharePoint Calculated Field Time Format Formula
- Add it in the formula section.
- Set the data type returned from this formula to Number with 0 number of decimal places.
- Save the formula.
Test the Time Format SharePoint Calculated Field Formula
- Add or edit a new item in your list.
- The newly calculated field should calculate the time as an hour and minutes based on the assigned number in the minute’s column.
Also, you can use any online converter tool to make sure that the calculation has been done successfully.
Time Format SharePoint Calculated Field Formula Explanation
I calculated the hours by dividing the number of minutes by 60 (Minutes / 60) then I used INT function to round down the result to the nearest integer.
I calculated the Minutes by getting the fraction result of (Minutes / 60) then multiply this fraction to 60.
Note: Use ROUND(number,num_digits) function to round a specific number to a specified number of digits, when the number of digits is equal to zero so it round to the nearest integer as I did in my formula.
In this article, I have explained How to
- Convert number field to Time format in SharePoint Calculated Field.
- Use the INT function in the calculated field.
- Use the ROUND function in the calculated field.
- SharePoint Online.
- SharePoint 2019.
- SharePoint 2016.
- SharePoint 2013.
- SharePoint 2010.
- Check my answer at Best field type to store videos & Audios duration and Please don’t forget to upvote it if it helped you 🙂