Why Calculated Column Returns Wrong Date

You created a calculated column in SharePoint to display a future date, a past date, or a date difference, but the result is off by one day, shows a serial number, or returns an error. This happens because SharePoint stores dates as serial numbers in Coordinated Universal Time and applies the site collection time zone only when rendering the value. This article explains the root cause of date miscalculations in calculated columns and shows you how to fix the formula to get the correct date.

Why SharePoint Calculated Columns Show the Wrong Date

SharePoint stores every date and time value as a serial number in Coordinated Universal Time. When you create a calculated column that references a date column, the calculation runs against the UTC serial number, not the formatted date you see in the list. The site collection time zone setting tells SharePoint how to display the date, but the calculated column formula does not automatically apply that time zone offset. This mismatch causes the displayed date to be off by one day when the site time zone is behind or ahead of UTC.

Another common cause is using text strings in date arithmetic. SharePoint expects numeric values for date math. Writing a formula like =[Start Date]+"30" treats the number as text and returns a serial number or an error. The same problem occurs when you use Today() or Now() in a calculated column. These functions return the current date in UTC, which may differ from the date in your local time zone.

A third cause is the way SharePoint calculates date differences. The DATEDIF function works correctly, but the simple subtraction =[End Date]-[Start Date] returns a decimal number representing the fraction of a day. Without rounding, the result appears as a decimal instead of a whole number of days.

Steps to Fix a Calculated Column That Returns the Wrong Date

1. Check the site collection time zone
   Go to SharePoint admin center > Active sites > select your site > Settings > Regional settings. Note the time zone. If the site time zone is not UTC, the calculated column may display dates that are one day off. To work around this, add or subtract the time zone offset in the formula. For example, for Eastern Standard Time (UTC-5), use =[Start Date] - 5/24 to adjust the displayed value.
2. Use the DATE function to create a date from parts
   If you are building a date from year, month, and day columns, use the DATE function instead of concatenating strings. Example: =DATE([Year],[Month],[Day]). This returns a proper date serial number that SharePoint can calculate correctly.
3. Add a number, not text, to a date
   To add 30 days to a start date, write =[Start Date]+30. Do not quote the number. If you write =[Start Date]+"30", SharePoint treats the 30 as text and returns an error or a serial number.
4. Use DATEDIF for date differences
   To calculate the number of days between two dates, use =DATEDIF([Start Date],[End Date],"d"). The “d” argument returns whole days. For months, use “m”. For years, use “y”. This avoids the decimal result that simple subtraction produces.
5. Round the result of date subtraction
   If you must use subtraction, wrap the result with the INT function to remove the decimal portion. Example: =INT([End Date]-[Start Date]). This returns a whole number of days.
6. Avoid Today() and Now() in calculated columns
   These functions return the UTC date and time, which may cause the calculated column to show a different date than expected. SharePoint also recalculates these values only when the item is modified, not on every page load. Use a workflow or Power Automate flow to get the current date in the correct time zone instead.

Common Date Calculation Mistakes and Their Fixes

Calculated column shows a five-digit number instead of a date

This happens when the formula returns a serial number that SharePoint does not recognize as a date. The most common cause is using a text string in the formula. For example, =[Start Date]+"7" returns a serial number. Remove the quotation marks: =[Start Date]+7. If the column still shows a number, change the column type from Single line of text to Date and Time in the column settings.

Date is off by one day after adding days

The site collection time zone is likely different from UTC. For example, if your site uses Pacific Standard Time (UTC-8), adding 30 days to a date may show the result as the previous day. Add or subtract the time zone offset in the formula. For PST, use =[Start Date]+30-8/24. Test with a few dates to confirm the adjustment is correct.

DATEDIF returns an error

The DATEDIF function requires the start date to be earlier than the end date. If the start date is after the end date, the function returns an error. Use the IF function to swap the dates when needed: =IF([Start Date]<[End Date],DATEDIF([Start Date],[End Date],"d"),DATEDIF([End Date],[Start Date],"d")). This formula always returns a positive number of days.

Calculated column does not update when the source date changes

Calculated columns recalculate only when the item is saved. If you change a date column in the list and the calculated column does not update, edit and save the item again. For automatic updates, consider using a Power Automate flow that updates a text column with the calculated date.

Calculated Column vs Power Automate for Date Calculations

Item	Calculated Column	Power Automate
Time zone handling	Uses UTC; must manually offset	Can use local time zone with convertTimeZone action
Recalculation	Only when item is saved	Runs on trigger (item created, modified, or scheduled)
Complex date logic	Limited to formula functions	Supports expressions, conditions, and loops
Real-time display	Shows in list view immediately	Updates a column after flow runs (may have delay)

For simple date addition or subtraction where the time zone is consistent, a calculated column with the correct offset works well. For complex logic, such as calculating business days or handling multiple time zones, use Power Automate.

You can now correct date calculations in SharePoint by using numeric values instead of text strings, adjusting for time zone offsets, and choosing the right function for date differences. Start by checking the site collection time zone and updating your formula to include the offset. For date differences, use DATEDIF with the IF function to avoid errors. If the formula becomes too complex, move the calculation to a Power Automate flow for better control over time zones and update timing.