How to Fix SharePoint Calculated Column One-Hour Time Error
Question details
The user needs a reliable formula to calculate the exact elapsed time between a start and end date in SharePoint, avoiding an incorrect one-hour offset.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Calculating the precise difference in days, hours, and minutes between two date-time columns in a SharePoint list.
- Observed behavior
- Default calculations sometimes produce an incorrect one-hour time difference, often linked to internal time-zone or Daylight Saving Time handling.
Ensure you have Edit permissions for the SharePoint list and verify that the 'Start date' and 'End date' columns are formatted as Date and Time.
Apply the Concatenate Text Formula for Precise Time Calculation
Use a combination of CONCATENATE, INT, and TEXT functions to accurately extract elapsed days, hours, and minutes.
This formula calculates the exact elapsed time by separating the days from the remaining hours and minutes. Converting the output to text prevents SharePoint from applying default time-zone shifts, which is the primary cause of the one-hour offset error.
Navigate to your SharePoint list, click the gear icon in the top right for Settings, and select 'List settings'. Under the Columns section, click on your calculated column.
In the Formula box, enter the following code: =CONCATENATE(INT([End date]-[Start date])," Days ",TEXT([End date]-[Start date],"h"" hrs ""m"" mins""))
Check that '[End date]' and '[Start date]' match the exact spelling of the columns in your list. If your columns are named differently, replace these brackets with your specific column names.
Scroll down to 'The data type returned from this formula is', select 'Single line of text', and click 'OK' to save your changes.
Manage Exported SharePoint Lists Seamlessly with WPS Office
When you export SharePoint lists to spreadsheets for deeper data analysis, pivot tables, or reporting, WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office.
- 1. Export Data: In your SharePoint list, click 'Export to Excel' to download your data as an .iqy or .xlsx file.
- 2. Install WPS Office: Download and install WPS Office Free on your Windows, Mac, or Linux device.
- 3. Open and Analyze: Open the exported file using WPS Spreadsheet to securely analyze your date-time calculations with full formula compatibility.
Frequently Asked Questions
Why does my SharePoint calculated column show a one-hour difference?
This error typically occurs because SharePoint stores date and time values in Coordinated Universal Time (UTC) internally. During calculation, Daylight Saving Time (DST) shifts or regional server misconfigurations can cause a one-hour offset. The text concatenation formula bypasses this internal conversion.
Can I output the time difference result as a Number instead of Text?
Yes. If you need the result for further mathematical calculations, use a simpler formula like =([End date]-[Start date])*24 to get total hours, and set the return data type to 'Number'. However, this may reintroduce the time-zone offset risk depending on your server settings.
How do I fix syntax errors when saving my formula in SharePoint?
Syntax errors often happen due to mismatched column names or regional settings. If your SharePoint site is set to a region that uses commas as decimal separators (like many European countries), you must use semicolons (;) instead of commas (,) to separate function arguments in your formula.
