SharePoint Document Issues

How to Calculate Duration in Days, Hours, and Minutes in SharePoint

John Wilson Oct 22, 2026 868 views

Question details

The user needs to calculate the exact elapsed time between two date and time columns in a SharePoint list and format the output to explicitly show days, hours, and minutes.

How to Calculate Duration in Days, Hours, and Minutes in SharePoint

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Tracking elapsed time or measuring durations between a defined start date and an end date (or current date) within a SharePoint list.
Observed behavior: Users require a specific formula to extract and concatenate the date and time differences into a readable string like 'X Days Y hrs Z mins' because standard calculated columns return raw numerical differences.

Before you start

Ensure you have at least 'Edit' or 'List Owner' permissions for the SharePoint list to create or modify calculated columns. Verify that your Start Date and End Date columns are configured to include both Date and Time.

Solution 1Recommended

Use a Calculated Column Formula for Start and End Dates

This is the standard and most reliable method for calculating the exact duration between two static date and time columns in SharePoint.

By utilizing a combination of the INT, TEXT, and CONCATENATE functions, SharePoint can calculate the numerical difference between two dates and format it into a human-readable text string.

Navigate to List Settings

Open 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., 'Duration') and select 'Calculated (calculation based on other columns)' as the column type.

Input the calculation formula

In the Formula box, paste the following exact formula: =CONCATENATE(INT([End date ]-[Start date ])," Days ",TEXT([End date ]-[Start date ],"h\" hrs \"m\" mins\"")). Be sure to replace '[End date ]' and '[Start date ]' with the exact names of your columns.

Set the data type returned

Under 'The data type returned from this formula is', select 'Single line of text', then click 'OK' to save the column.

Check Column Names: Make sure the column names in your formula exactly match your list columns, including any spaces or special characters.

Solution 2

Calculate Elapsed Time Until the Current Date

Use this workaround if you need to calculate the elapsed time from a start date up to the current date and time.

Free Microsoft Office alternative

Manage Complex Spreadsheets and Time Tracking with WPS Office

If SharePoint's list limitations are making time and date tracking too complicated, consider managing your complex logs and trackers in WPS Spreadsheet. It offers powerful, real-time formula calculations and serves as a highly compatible, lightweight alternative to Microsoft Office.

1. Download and Install WPS Office: Visit the official WPS website, download the free version, and install it on your device.
2. Open WPS Spreadsheet: Launch the application and select Spreadsheet to create a new tracker or open your exported SharePoint list.
3. Apply Dynamic Formulas: Use functions like =NOW() or =TODAY() in WPS Spreadsheet, which update automatically every time you open or refresh the file, bypassing SharePoint's static calculation limits.

Seamless compatibility with Microsoft Excel (.xlsx) formatsRobust built-in formulas for dynamic real-time date and time calculationsLightweight application that runs smoothly on Windows, Mac, and LinuxFree and intuitive interface for easy data tracking and team sharing

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the [Today] function update automatically in a SharePoint calculated column?

SharePoint calculates formulas only at the moment an item is created or when its properties are explicitly edited and saved. Using [Today] in a calculated column will only reflect the exact date the item was last modified, not the continuously changing real-time date.

Can I hide the seconds from my time duration calculation?

Yes. The TEXT portion of the provided formula specifically formats the time output as "h\" hrs \"m\" mins\"". This formatting inherently ignores the seconds value and only displays the calculated hours and minutes.

What happens if my Start Date is later than my End Date in the formula?

If the Start Date is later than the End Date, the mathematical difference is negative. The formula will return a negative number for the days, which can result in unexpected or broken text formatting. You can prevent this by wrapping your calculation in an IF function to check if the End Date is greater than the Start Date.

Document & File Issues