All-Day Events Sort Wrong in SharePoint Lists
Understanding SharePoint List Sorting Issues with All-Day and Timed Events
If you’ve ever worked with SharePoint calendars or lists, you may have noticed a strange behavior when sorting events by End Date—all-day events don’t always appear where you expect them, especially when mixed with normal timed events. This can lead to confusion when trying to organize or report on upcoming events.
Let’s break down why this happens and how to understand it.
Position | Event Name | Type | End Date (List-View UTC+2) | End Date (UTC) | Notes on Sorting |
|---|---|---|---|---|---|
1 | Event A | All-Day | 2025-06-21 23:59 | 2025-06-21 23:59 | ✅ Correct (first day) |
2 | Event B | All-Day | 2025-06-22 23:59 | 2025-06-22 23:59 | ✅ Correct |
3 | Event C | Timed | 2025-06-24 01:00 | 2025-06-23 22:59 | ❌ Appears before Event D, but logically should be after D’s timed start |
4 | Event D | All-Day | 2025-06-23 23:59 | 2025-06-23 23:59 | ❌ Appears after Event C in SharePoint End Date sorting |
5 | Event E | All-Day | 2025-06-24 23:59 | 2025-06-24 23:59 | ✅ Correct |
How SharePoint Stores Dates
The core of the issue lies in how SharePoint handles dates internally:
- All-day events are stored as starting at 00:00 UTC and ending at 23:59 UTC, regardless of the user’s local time.
- Timed events store their actual start and end times in UTC.
When you view your list or calendar, SharePoint converts UTC times to your local time zone . This ensures that events display correctly on the calendar according to your location. However, the list sorting behaves differently for Start Date and End Date :
- Sorting by Start Date works as expected because SharePoint uses the stored UTC start date, which aligns with the local display for all-day events.
- Sorting by End Date, however, uses the raw UTC end date, not the local display, causing all-day events to sometimes appear out of order relative to timed events.
Microsoft has officially confirmed that this behavior is expected: the list sort always uses the stored UTC value, not the localized display value.
👉 [Microsoft Support Confirmation](https://learn.microsoft.com/en-us/answers/questions/5168867/sharepoint-calendar-showing-events-on-day-created)
Why It Matters
For anyone managing events, deadlines, or schedules in SharePoint, this sorting problem can lead to:
- Missed deadlines because all-day events appear in the wrong order in the list.
- Confusion when exporting lists to reports or Excel, as the sorting may not match the visual calendar.
- Frustration for end-users who expect the list and calendar to match.
Workarounds
Here are some ways to mitigate the issue:
Calculated Columns Create a new column that adjusts the UTC end date to the local time zone using a formula like
=[End Date]+TIME(2,0,0)