Formatting Issues

How to Fix Incorrect Month Formatting in SharePoint Date Columns

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to correct a display issue in SharePoint where dates falling on the first day of the month are incorrectly evaluated as the previous month in conditional formatting.

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Applying JSON conditional formatting to a date column to evaluate or display the month.
Observed behavior: Because SharePoint stores dates at midnight in UTC, time-zone conversions cause dates on the first of the month to shift backwards by a few hours, making the getMonth function return the previous month.

Before you start

Ensure you have the necessary list editing permissions in SharePoint to access and modify column formatting settings.

Solution 1Recommended

Add Milliseconds to Offset the Time-Zone Conversion

Compensate for the backwards time-zone shift by adding the equivalent of one full day (in milliseconds) to the date before applying the getMonth function.

SharePoint handles dates internally using Coordinated Universal Time (UTC). When a user inputs a date without a specific time, it defaults to midnight UTC.

Depending on your local time zone settings, this midnight value may be converted into the evening of the previous day. To fix this in JSON formatting, you can manually push the date forward by 24 hours (86,400,000 milliseconds) right before extracting the month.

Access Column Formatting

Navigate to your SharePoint list, click on the column header of the affected Date column, select 'Column settings', and click 'Format this column'.

Open Advanced Mode

At the bottom of the formatting pane, click on 'Advanced mode' to view and edit the JSON code.

Update the getMonth Function

Locate any instances of the expression `getMonth(@currentField)`. Update them by adding the millisecond value for one day, so it looks like `getMonth(@currentField + 86400000)`.

Save and Verify

Click 'Save' and check your list view. The dates falling on the first day of the month should now correctly reflect the intended month.

Understanding the Offset Value: The number 86,400,000 represents exactly one day in milliseconds (24 hours × 60 minutes × 60 seconds × 1000 milliseconds).

Free Microsoft Office alternative

Simplify Data Tracking with WPS Office

Dealing with complex JSON expressions and time-zone conversions in SharePoint can be frustrating. For straightforward data tracking and easy date formatting, WPS Spreadsheet provides a lightweight, intuitive environment without the need for custom coding.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open WPS Spreadsheet: Launch the application and open your data lists or create a new workbook.
3. Format Dates Easily: Right-click your date cells, select 'Format Cells', and choose your preferred date display without writing any code.

Fully compatible with Microsoft Excel (.xlsx) and Word (.docx) formats.Manage and format dates easily through a standard user interface.Free, lightweight, and requires minimal system resources.Familiar layout ensures a seamless migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why does SharePoint change the date I entered?

SharePoint stores date and time values in Coordinated Universal Time (UTC). When the system displays this stored value, it converts it to your site's regional time zone. If your time zone is behind UTC, midnight shifts to the previous day.

Does adding 86400000 milliseconds alter the actual stored date?

No. The calculation only offsets the value temporarily within the specific conditional formatting expression for display purposes. The underlying data stored in your SharePoint list remains completely unaffected.

Can I fix this by changing the site's regional time zone settings?

While changing the site's regional time zone will alter how times are displayed, it will affect all time values for all users globally across the site, which often causes broader scheduling issues. Using the JSON formatting fix is a safer, localized solution.

Document & File Issues