'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Convert Minutes to Hours and Minutes in a SharePoint List
Question details
The user needs to format a numeric column storing total minutes into a readable text string displaying hours and minutes.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Displaying time duration data stored as raw minutes in a user-friendly format, such as converting 150 minutes to 2 hours and 30 minutes.
- Observed behavior
- The list currently displays only numeric minute values, requiring a formula or calculation to display the desired format.
Ensure you have the necessary permissions to modify list settings and add calculated columns to your SharePoint list.
Use a Calculated Column Formula
You can create a calculated column in SharePoint that mathematically converts the minutes into hours and concatenates the remaining minutes into a formatted text string.
This solution utilizes standard formula functions (INT and MOD) that are natively supported by SharePoint calculated columns. It requires no custom code and updates automatically whenever the source minutes value is modified.
Navigate to your SharePoint list. Click on the Settings gear icon in the top right corner and select 'List settings'.
Scroll down to the Columns section and click on 'Create column'.
Enter a name for your new column, such as 'Formatted Duration'. Under the column type options, select 'Calculated (calculation based on other columns)'.
In the Formula text box, input the following formula: =INT([Time]/60)&" Hours, "&MOD([Time],60)&" Minutes". Note: Replace '[Time]' with the exact name of your original numeric column.
Set the data type returned from this formula to 'Single line of text', then click 'OK' at the bottom of the page to apply your changes.
Manage and Calculate Time Data Offline with WPS Office
If you are exporting SharePoint list data or need a powerful spreadsheet tool to analyze and format time data offline, WPS Office provides a lightweight, highly compatible alternative to Microsoft Office. It supports the exact same formulas for time calculation without the subscription costs.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Run the Installation: Open the downloaded installer file and follow the on-screen prompts to complete the setup.
- 3. Open and Analyze Data: Launch WPS Spreadsheet, import your exported list data, and apply your duration formatting formulas.
Frequently Asked Questions
How do I handle empty values in my SharePoint time formula to avoid errors?
You can wrap your calculated formula in an IF statement to check if the source column is blank. For example: =IF(ISBLANK([Time]), "", INT([Time]/60)&" Hours, "&MOD([Time],60)&" Minutes").
Can I perform this same minute-to-hour conversion in a spreadsheet?
Yes. The exact same logic applies in spreadsheet software. The formula =INT(A2/60)&" Hours, "&MOD(A2,60)&" Minutes" works perfectly in both Microsoft Excel and WPS Spreadsheet, assuming your minutes are stored in cell A2.
Why am I getting a syntax error when saving my calculated column in SharePoint?
Syntax errors often occur due to regional settings. If your SharePoint site uses a locale that requires semicolons instead of commas to separate formula arguments, replace the commas in the formula with semicolons. Also, ensure your column name exactly matches the text inside the brackets.
