SharePoint Date column - TimeZone / Daylight Saving Time issues

(1) Share

Report

Posted on by Mx81

860

Hello,

we have a SharePoint with regional setting: TimeZone UTC+1.

In a SharePoint list, we have 3 dates: BirthDate, NameDate and ContractDate. We store only date information. So no time information is actively stored.

I know, SharePoint is storing DateTimes in UTC, but the problem is: depending on the date, SharePoint seems to store the information in "normal" or "summer"-time - so more or less the same as daylight saving time in the US.

Example:

As you can see, the time difference to the actual entered date in SharePoint is between 1 or 2 hours.

How can I handle this? I want to do my next steps (calculations, conditions) based on the "correct" day.

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

David- 1,254 on at

Like (1)

Report
Copy link

Link copied!

I don't know if this will solve your problem, but as you mentioned SharePoint saves all dates in UTC. So you could use the Convert time zone action to convert from UTC to your time zone.

And while you may not be storing the time in your list, SharePoint saves the time as 12 midnight UTC and then offsets it based on the Time Zone of the site or the user profile.

Converting time zone in Power Automate - Power Automate | Microsoft Docs

This old article helps to explain the nightmare of working with dates and times in SharePoint: SharePoint time, is not your time, is not their time. – Veni, Vidi, Vici (julieturner.net). Good luck.

Was this reply helpful? Yes No
Mx81 860 on at

Like (0)

Report
Copy link

Link copied!

unfortunately, the convert time zone action didn't solve my problem. because depending of the time difference, the convert gives me the wrong date back.

Example with convert from UTC to UTC+1:
birth date in SharePoint stored as: 1968-03-15T23:00:00Z --> 1986-03-16 <-- correct
name date in SharePoint stored as: 2010-06-28T22:00:00Z --> 2010-06-28 <-- not correct, I need the 2010-06-29

Of course, I can just add always 2 (or even more) hours in an expression, but this should not be an manual process.

Was this reply helpful? Yes No
Norro 33 on at

Like (1)

Report
Copy link

Link copied!

Microsoft shouldn't be applying timezone shifts to fields set as date only, but they do.

So treat timeless dates as broken and store date only info with a time of midday that you ignore. Then the DST timezone shift won't bring it across a date threshold.

Was this reply helpful? Yes No
MichaelBoonie 16 on at

Like (0)

Report
Copy link

Link copied!

So, if I understand correctly, it is by Microsoft design that someone in Central Time simply cannot possibly see the same event-date that a person in Eastern Time entered in a SharePoint list? THAT'S useful.
I have a list that stores a date with no time (no automation, no PowerApps forms, no nuthin'!) but where I see April 1, my teammate in St. Louis sees March 31 by design because Microsoft thinks we're a day apart?????
Kinda sucks, and I now have an angry customer who can't use the list. Well done, Microsoft.

Was this reply helpful? Yes No