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

Hi GTMSW ADM,

Your validation formula is incorrect, try =Today=TODAY()

If the issue still persists, change to another time zone and change back, see if there is any improvement. 

Regards,

Eric

Answer 3

Hi Eric, the formula in my screenshot is not a validation, it's the formula for a new calculated column. 

As I mentioned, the reason I am creating this column is to check what date Today() function is returning, to check why my validation is not working. Since you are asking about validation formula, I will explain from the beginning.

In my Sharepoint list, I have created a column called 'Date' for user to pick a date.

I want to set a validation such that the user is not able to choose future date (i.e. user can pick any date that is today or earlier).

So I set a simple validation formula: = [Date] <= Today()

But this validation is not working. When I try to create a new list item with today's date in 'Date' field, the validation stops me.

So I decided to find out what value Today() function is returning, and created a calculated column so that the returned value is visible to me. As I shown in my previous screenshots, it is showing the wrong date.

How can I make the Today() date accurate?

Answer 4

I have tried changing the timezone to another zone and changing back again as suggested, Today() still giving me the wrong date.

Answer 5

Thanks for come back to me. 

An easy way is to use = [Date] <= Today()+1 

But it didn't solve the issue itself, let's do some troubleshooting. 

1.Please change Locale to your real location and see if there is any improvement.   

2.Please check if the same issue happen in other SharePoint sites.

3.Please create a new SharePoint list and check if you can reproduce the issue.

If the issue happen in other sites and new-created sites, which is a tenant-level issue. In order to protect your privacy, I kindly suggest you creating a ticket in Office 365 Admin center under support section, and our engineers will collect your tenant information and provide more assistance at background. 

Thanks for your cooperation. 

Regards,

Eric