Sharepoint: Today() function not showing correct date even though timezone already set in Site Regional Settings

Question

Hi,

I have a Sharepoint list where I want to insert a validation that compares to today's date using the Today() function. The validation was not working so to troubleshoot this, I decided to create a calculated column to show today's date.

Added a column in Sharepoint list:

What I found was the date returned by Today() function was not correct. It shows 22nd March even though the local time is 23rd March.

We have already set the timezone of this Sharepoint site to the local timezone. 

Are there other timezone settings we need to set? Or is there a function to convert the Today() date to local timezone?

Thank you for your help.

Answer 1

Hi Eric, changing the Locale did not work as well.

I created a ticket for this issue.

Just for sharing, the workaround for this is to use the [Created] date as today's date.

Today() function is still returning the wrong date, timezone wise.

Example, if my validation formula is = [myDateColumn] <= Today()

The workaround is to have = [myDateColumn] <= [Created]

However this workaround works if you want your validation to work only during creation of a new list item. Might not work for all scenarios.

Answer 2

Thanks Eric, the '+1' method won't work as it will mean the validation works during the first half of the day, but not during the second half.

I will check whether I can reproduce on other sites.

For 'Locale', where do I find that setting? Is it a site-level setting?

Thanks.

Answer 3

It's under the time zone setting.

Regards,

Eric

Answer 4

Thanks for your sharing. 

I mask it as answer for other users to get the workaround conveniently. 

Regards,

Eric

Answer 5

Hi Eric, changing the Locale did not work as well.

I created a ticket for this issue.

Just for sharing, the workaround for this is to use the [Created] date as today's date.

Today() function is still returning the wrong date, timezone wise.

Example, if my validation formula is = [myDateColumn] <= Today()

The workaround is to have = [myDateColumn] <= [Created]

However this workaround works if you want your validation to work only during creation of a new list item. Might not work for all scenarios.

Hi GTMSW ADM,

I realize you marked the question as answered and logged a ticket for the issue. Did you get a resolution on the ticket?

I also had this issue and did the following to get around it. I added a calculated column named Now formatted as date and time to see what SharePoint Online thinks the current date and time was. A simple =Now() calculation returned 01/06/2020 00:15 while the actual date and time was 01/06/2020 09:15. 

I then just offset the date and time by 9 hours using the following calculation =NOW()+(1/24/60*540) and saved the calculated column as date only.