Others

How to Convert SharePoint List Duration to Days, Hours, and Minutes

Adam Davis Oct 21, 2026 868 views

Question details

The user needs a calculated column formula to accurately compute the time duration between two date and time values in SharePoint Lists, displaying the output strictly as days, hours, and minutes.

How to Convert SharePoint List Duration to Days, Hours, and Minutes

Product: SharePoint Lists
Device & OS: not provided
Scenario: Calculating the precise duration between an 'Injection Start' and 'Injection End' column in a SharePoint List without calculation errors.
Observed behavior: Standard date subtraction returns an incorrect day and hour count when the total duration is simply multiplied by 24. The difference must be mathematically separated into whole days, remaining hours, and remaining minutes.

Before you start

Verify that your SharePoint List has two dedicated date columns (e.g., 'Injection Start' and 'Injection End') and that both are configured to include Time values in their column settings.

Solution 1Recommended

Apply the Custom Separation Formula in a Calculated Column

Use a combination of ROUNDDOWN and ROUND functions in a calculated column to accurately extract the whole days, remainder hours, and remainder minutes separately.

SharePoint date math calculates differences as fractions of a day. To prevent incorrect totals when multiplying by 24, you must isolate the whole days first, subtract them to find the remaining hours, and repeat the process to find the remaining minutes.

Access List Settings

Navigate to your SharePoint List, click the gear icon in the top right corner, and select 'List settings'.

Create a New Calculated Column

Scroll down to the Columns section and click 'Create column'. Name your new column (e.g., 'Total Duration') and select 'Calculated (calculation based on other columns)' as the column type.

Insert the Formula

In the formula box, paste the following exact string: =ROUNDDOWN([Injection End]-[Injection Start],0)&" days "&ROUNDDOWN((([Injection End]-[Injection Start])-ROUNDDOWN([Injection End]-[Injection Start],0))*24,0)&" hours "&ROUND(([Injection End]-[Injection Start]-ROUNDDOWN([Injection End]-[Injection Start],0)-ROUNDDOWN((([Injection End]-[Injection Start])-ROUNDDOWN([Injection End]-[Injection Start],0))*24,0)/24)*24*60,0)&" minutes"

Adjust Column Names and Save

If your start and end columns have different names, replace '[Injection Start]' and '[Injection End]' in the formula with your exact column names. Set the data type returned from this formula to 'Single line of text' and click 'OK' to save.

Apply the Custom Separation Formula in a Calculated Column

Formula Validation: Ensure there are no extra spaces inside the brackets of your column names. The formula relies on exact matches to reference your existing date and time data.

Free Microsoft Office alternative

Handle Complex Data Calculations Easily with WPS Office

While SharePoint Lists manages online records, analyzing exported list data often requires a robust spreadsheet tool. WPS Office provides a powerful, free alternative to Microsoft Office, offering identical formula capabilities for your advanced date and time calculations.

1. Download and Install: Visit the official WPS Office website to download the free suite and install it on your device.
2. Export and Open Data: Export your SharePoint List to an Excel file, right-click the file, and choose 'Open with WPS Spreadsheet'.
3. Analyze and Calculate: Use WPS Spreadsheet's built-in date formatting and functions to quickly verify your list duration data.

Fully compatible with Microsoft Excel (.xlsx) files exported from SharePoint.Supports advanced mathematical, date, and time calculation formulas natively.Free, lightweight, and features a familiar user interface for seamless workflow transitions.

microsoft office alternative - wps office

Frequently Asked Questions

Why does a simple subtraction formula give incorrect hours in SharePoint?

SharePoint calculates date and time differences as a fractional day (e.g., 1.5 days). If you simply multiply the entire fraction by 24, it treats the entire duration as hours rather than separating the whole days from the remainder, leading to inflated hour counts.

Can I use this exact duration formula in Excel or WPS Spreadsheet?

Yes. The mathematical logic using ROUNDDOWN and ROUND to separate days, hours, and minutes works identically in standard spreadsheet software like WPS Spreadsheet and Microsoft Excel, provided you update the cell references (e.g., replacing [Injection Start] with A2).

What happens if the End Date is earlier than the Start Date?

If the End Date is earlier than the Start Date, the formula will output negative values. To prevent this, you can wrap the calculation in an IF statement to check whether [Injection End] > [Injection Start] before running the calculation.

Do I need to check a specific setting for the time calculation to work?

Yes, both of your date columns must be explicitly configured to include time. In SharePoint column settings, ensure the 'Date and Time Format' is set to 'Date & Time' instead of 'Date Only'.

Office Quick Solutions