How to Fix SharePoint TODAY and NOW Returning Unexpected Dates
Question details
The user needs to correct list validation formulas using TODAY and NOW, which are returning incorrect dates due to time zone discrepancies.

- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating validation rules in a SharePoint list using date functions (like TODAY and NOW) to ensure an observed date is not set in the future.
- Observed behavior
- Before a specific local time (e.g., 11:30 AM India Time), the TODAY() and NOW() functions return the previous day's date or a future date because SharePoint evaluates them against the server's time zone instead of the user's local time zone.
Ensure you have Site Owner or Administrator permissions for the SharePoint site, as modifying the default regional settings requires elevated access.
Update SharePoint Site Regional Settings
Adjust the SharePoint site's default time zone to match your local region (e.g., India Standard Time) so TODAY and NOW formulas evaluate correctly.
SharePoint calculates list validation formulas, such as TODAY() and NOW(), based on the site's regional settings rather than your computer's local clock. If the site is configured for a different time zone (like Pacific Time), it will cause a delay in date changes for users in other regions.
Click the gear icon (Settings) in the top-right corner of your SharePoint site and select 'Site information', then click 'View all site settings'.
Under the 'Site Administration' section, locate and click on 'Regional settings'.
In the Time Zone section, select the correct local time zone from the drop-down menu (for example, '(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi').
Scroll to the bottom of the page and click 'OK' to apply the new time zone configuration to your site formulas.

Adjust Personal User Profile Time Zone
Override the default SharePoint site time zone settings for your personal account if you are working remotely in a different region.
Use WPS Office for Reliable Local Spreadsheet Data Management
If you are struggling with cloud server time zone conflicts in SharePoint or Microsoft 365, consider managing your complex datasets locally with WPS Office. WPS perfectly supports Microsoft Excel formats and evaluates date functions like TODAY() and NOW() strictly based on your device's local system time, guaranteeing complete accuracy without server delays.
- 1. Download WPS Office: Visit the official WPS website to download and install the free, lightweight suite on your device.
- 2. Open your Spreadsheet: Export your SharePoint list or open your existing Excel workbook directly within WPS Spreadsheet.
- 3. Apply Formulas Accurately: Enter the TODAY() or NOW() formulas; WPS Spreadsheet will instantly pull your accurate local device time without cloud server interference.

Frequently Asked Questions
Why does TODAY() show yesterday's date in SharePoint?
SharePoint calculates the TODAY() function based on the server's Regional Time Zone settings, not your computer's local clock. If the server is in a time zone behind yours (e.g., US Pacific Time vs. India Standard Time), it will display yesterday's date until the server's clock officially reaches midnight.
What is the difference between TODAY() and NOW() in list validation?
The TODAY() function returns only the current date (e.g., 10/25/2023), whereas NOW() returns both the current date and the specific time. For columns formatted as 'Date Only', it is highly recommended to use TODAY() to avoid validation errors caused by hidden time values.
Can I force SharePoint calculated columns to use my local time zone?
Calculated columns and validation rules strictly utilize the SharePoint Site's Regional Settings. While you can change your personal viewing time zone, formula calculations will always rely on the site-wide default. A Site Administrator must update the site's default Time Zone to fix formula offsets for everyone.




