SharePoint Date Column Validation issue

[Damien Flood]

Hi,

I have date column in a list which I would like to have a validation on where users can't select a date which is greater than todays date. I have been using the following formual with some success. 

=[Document date]<=TODAY()

For some reason when a user enters todays date in the field before e.g. 09:00 CET it does not see it as <= Todays date. If they enter todays date after e.g. 09:00 CET it will recognise it as todays date.

Any ideas?

[#2]

Hi @Damien Flood,

Is your date field a date field or a date and time field.

If you set the default value of the field to TODAY() then you will find that the TODAY() returns a date with 12:00 AM as a default time in Date time fields. So that is the beginning of the day.

When I tried setting the default value to Now() then I got the current time somewhere in the world (7.54am) while my local time is 3:55pm.

So now back to your problem, you might want to set the default time of your date field to Now(). Make sure that you enable Date and time on the field for testing purposes.

What is the current time according to your SharePoint tenant? Does it match your expected time?

[#3]

Thanks @Pieter Veenstra

Sorry for the confusion and hope the below makes sense.

The field is Date only.

And if I set the default to TODAY it returns UTC "<d:Date m:type="Edm.DateTime">2017-08-10T22:00:00Z</d:Date>"

The regional setting for the site collection is UTC + 1 which is correct taking into account DST. So the date displays as 2017-08-11 00:00 as you mentioned.

So with regards the validation if TODAY() is 2017-08-10T22:00:00Z (2017-08-11 00:00) I am confused as to why when a user chooses today e.g. 2017-08-11 from the date picker after a specific time during the day of 2017-08-11 it gives the error message that you need to pick todays date or earlier.

If I set to NOW() there is about -9 hour difference compared to local time.

Cheers

[#4]

Hi @Damien Flood,

I think that the 9 hours difference reported by Now and the before/after 9am is not a conincidence.

I remember a few years ago I had a similar issue with comparing times. I can't exactly remember how I resolved it, but I think I used the creation/modification time in the item to get the current time rather than the functions Today and Now.

[#5]

Had the same requirement, just remove the '=' and it works