How to Calculate Hours Between Date and Time Columns in SharePoint
Question details
The user needs to calculate the exact elapsed hours between separate Start Date, Start Time, End Date, and End Time columns in a SharePoint list.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating a calculated column in a SharePoint list to measure the time duration across multiple days.
- Observed behavior
- Subtracting time components directly yields incorrect results, such as returning 36 hours instead of 11 hours when the time period crosses midnight.
Ensure that your Start Date, Start Time, End Date, and End Time columns are properly populated in your SharePoint list, and verify whether your site's regional settings use commas or semicolons for formulas.
Combine Date and Time Fields to Calculate Total Hours
Merge the date and time values into a single entity before calculating the difference to avoid calculation errors when time shifts across midnight.
SharePoint handles dates and times as numeric values. If your date and time values are kept in separate columns, subtracting the time columns individually will break when a shift crosses midnight. By mathematically combining the End Date and End Time, and subtracting the combined Start Date and Start Time, you get the exact fractional days elapsed. Multiplying this by 24 converts the result into hours.
Navigate to your SharePoint list, click the gear icon in the top right corner, and select 'List Settings'.
Scroll down to the Columns section and click 'Create column'. Name your new column (e.g., 'Total Hours') and select 'Calculated (calculation based on other columns)' as the column type.
In the Formula box, enter the following exact formula: =((EndDate+EndTime)-(StartDate+StartTime))*24 . Replace the column names with the exact names used in your list.
Under 'The data type returned from this formula is', choose 'Number'. Set the number of decimal places you wish to display (e.g., 2 for fractional hours).
Click 'OK' at the bottom of the page to apply the new calculated column to your SharePoint list.
Analyze SharePoint Exported Data Easily with WPS Office
While SharePoint lists require complex online calculated columns, you can export your data and perform advanced time tracking and date calculations much easier using WPS Spreadsheet. WPS Office is a free, lightweight, and highly compatible alternative to Microsoft Office.
- 1. Export SharePoint Data: In your SharePoint list, click 'Export' and select 'Export to Excel' or 'Export to CSV'.
- 2. Open with WPS Spreadsheet: Launch WPS Office and open the downloaded spreadsheet file seamlessly.
- 3. Calculate Hours Offline: Use standard spreadsheet formulas like =((C2+D2)-(A2+B2))*24 and format the column as a Number to calculate hours instantly.
Frequently Asked Questions
Why does my SharePoint calculated column show 36 hours instead of 11?
This happens when the formula calculates the date and time separately without properly grouping them. If a time period crosses midnight, the raw hour subtraction returns a negative or oversized number. Combining the date and time fields before subtracting them resolves this.
Should I use commas or semicolons in my SharePoint formula?
It depends on your SharePoint site's Regional Settings. US settings typically use a comma (,) to separate function arguments, while many European or international settings require a semicolon (;).
Can I use the DATEDIF function to calculate hours in SharePoint?
No, the DATEDIF function in SharePoint is only designed to calculate the difference in days, months, or years. To calculate hours, you must use standard mathematical subtraction and multiply the final result by 24.
What data type should the hour calculation column be set to?
The calculated column should be set to return a 'Number' data type rather than Date/Time. You can also specify the exact number of decimal places depending on whether you need to see fractional hours (like 8.5) or rounded hours.
