I am trying to validate dates in a task request form that has a column of [Date of Request] (which would be the day that they are adding the task to the main hub.

I, then, want that [Date of Request] to see the [Requested Posting Date] column and not allow it to post a date that is before the [Date of Request].

I have tried everything I can from:

=[Requested Posting Date]<=[Date of Request] 


=[Requested Posting Date]<[Date of Request] 


[Due Date] > [Created] 

and none of those work for what I am trying to do here. I am doing all of these formulas in the validation settings section if that helps. Please help me if you can everything I try fails or just doesn't do anything, let me know how to resolve this and get it working. Just nothing will affect the date pickers whatsoever they do exactly the same thing no matter what formula I throw in, im completely lost, let me know Thanks!

  • Are you using the validation settings on the list settings page? (List Settings --> Validation Settings) Because that is the only one that allows you to compare 2 columns. Jul 15 '15 at 21:20
  • Yes I am writing these formulas on the list settings --> Validation settings --> Formulas. I haven't added any formulas to the column validation either and still nothing will be affected at all from the formulas I put in the validation settings area... Any ideas? Jul 15 '15 at 21:22
  • Is the column type for both the columns "Date and Time"? Jul 15 '15 at 21:27
  • Date of Request: Date and Time Requested Posting: Date and Time Jul 15 '15 at 21:28
  • Then, the second formula you posted, should definitely work i.e. =[Requested Posting Date]<[Date of Request] . I have just tested it. Jul 15 '15 at 21:30

=[Requested Posting Date]>=[Date of Request]
If I'm reading this right, you don't want to allow the Requested Posting Date to fall before the Date of Request. The validation fails only if the formula evaluates to FALSE. With your formula:
=[Requested Posting Date]<=[Date of Request]
a Requested Posting Date that falls before the Date of Request will evaluate to TRUE. Earlier dates have lower values.

You can confirm this is the issue by attempting three test cases: date before, date after, and same date.

  • Awesome that seemed to work for the most part, thank you so much for your help, my next question would be how can I set it so that they cannot choose a date in the past on the Date of Request field? Right now they could choose say July 15th and set it for being done on the 16th but that is in the past and I need them to be only able to set it for Todays date of the 16th and so on for each coming day without someone being able to change it? Jul 16 '15 at 17:09
  • You could use =[Date of Request]>=Today() , but then you'll get errors if you try to edit the item for another reason tomorrow, unless you push out the Date of Request whenever you edit. Since I don't think you want that, try =[Date of Request]>=Created . I think you'll have to add this to the list validation formula as well, so put both conditions in an AND function. The format would be =AND(TRUE,TRUE). Jul 16 '15 at 17:19
  • Where would I add the =[Date of Request]>=Created and the =AND(TRUE,TRUE) would I put those in the column validation area for each of the [Requested Posting Date] and [Date of Request] or would I put them under the validation settings where I put the last bit of code you gave me, a litte confused? I am a little new to Sharepoint so I am still trying to figure everything out... Thanks for all your help!!! Jul 16 '15 at 17:24
  • Or, you could add some type of Request Status column, and place this second check inside an IF function so that it only checks the Date of Request if the status is "New". Jul 16 '15 at 17:25
  • It would all be one formula in the list validation. So it would look like: =AND([Requested Posting Date]>=[Date of Request],IF([Request Status]="New",[Date of Request]>=Today(),TRUE)) Jul 16 '15 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.