In this post, we will explain How to convert Number to time in SharePoint Calculated Field
You might also like to read Supported and Unsupported Columns In SharePoint Calculated Column Formula
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:
in this section, we’ll show how to display a decimal as a time in SharePoint calculated column by doing the below steps:
- 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).
Steps
- Open SharePoint site.
- Click on the setting gear icon, click on “Add a new App”.
- Create a custom list.
In the newly created SharePoint List, we’ll create a new column for “Minutes” with Number datatype by doing the following:
Steps
- Open List Settings.
- Scroll down to columns, click on add a new column.
- The field data type must be a Number.
- The number of decimal places should be 0.
- 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.
- Or download the List Template with the time formula at Convert Minutes to Time format in SharePoint Calculated Field.
- 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.
- 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.
In this section, we’ll explain in details the formula used to Convert Number To Time in SharePoint Calculated Field.
Hour Calculation
we have calculated the hours by dividing the number of minutes by 60 (Minutes / 60) then we used INT function to round down the result to the nearest integer.
Minute Calculation
we have also 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.
Conclusion
In conclusion, we have learned How to Convert Number To Time in SharePoint Calculated Field, also we have explored the use of
- INT function in the SharePoint calculated field.
- ROUND function in the SharePoint calculated field.
Download
You can download the list template with the time formula from GitHub at Convert Minutes to Time format in SharePoint Calculated Field, also Please, don’t forget to Follow Me to get the latest updates.
Applies To
- SharePoint Online.
- SharePoint 2019.
- SharePoint 2016.
- SharePoint 2013.
- SharePoint 2010.
You might also like to read
- Using Lookup Field in Calculated Column SharePoint.
- Use SharePoint ID Field in Calculated Column.
- SharePoint Auto Populate Column based on another Column.
- Auto Serial Number in SharePoint New Form using JSOM
Have a Question?
If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.
Pingback: Date Conditional Formatting In SharePoint 2019 | SPGeeks
I was very happy to uncover this web site. I wanted to thank you for ones time just for this fantastic read!! I definitely enjoyed every part of it and I have you book marked to look at new information on your blog.
Nice article..!
Thanks! This worked out well for me, but I would like to show Seconds to hh:mm:ss. Is that possible?
Awesome article ❤️ love it
Welcome 🙂