SharePoint Date Column Validation issue

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 succe...

Lists

SharePoint Online

Damien Flood

Iron Contributor

Aug 11, 2017

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

Pieter Veenstra

MVP

Aug 11, 2017

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.

Forum Discussion

SharePoint Date Column Validation issue

Resources