SharePoint 2013: The formula cannot refer to another column.

Question

 I am trying to add some validations between two columns of a list [Start Date] and [End Date] with the formula below

=[Start Date] < [End Date]

however this produces

Sorry, something went wrong

The formula cannot refer to another column.  Check the formula for spelling mistakes or update the formula to reference only this column.

Technical Details

---

So I tried to test with the same column as below and it saved successfully ( although this is not useful as a formula )

=[Start Date] > [Start Date]

This gets more confusing because some videos on the net show that this is possible however on my Office365 account I do not see the Insert Column: part in my column settings

Source: https://www.youtube.com/watch?v=zm5vnSNESPI

Do you have an idea what am I doing wrong? I tried this both on Office 365 and our on premisses SharePoint and both have the same issue. If this is happened after some upgrades of SharePoint how does one achieve such cross column validations now-a-days? Thanks

Answer 1

Hey,

Instead, You can try the same formula in validation settings under general settings tab. To achieve the same do the following.

1. Go to List settings > General Settings > Validation settings.
2. Add your formula, save your changes and test.

Thanks.

Answer 2

Hi,

Can you please let me know what you are trying achieve from the formula?

Are you going to compare two dates and get true or false or something....please clarify?

Answer 3

Hi Stephan,

I agree with what Jaydeep said, you need to use the List Validation for validating two different columns and this formula will work in that case.

=[Start Date] < [End Date]

If you are validating DataType, IsNull or compare with any hardcode value then Column validation will work, but if it involves validation against other column value then List validation is required.

Best Regards,
Brij K

Answer 4

I don't want to let the user create an entry where the End date comes before the Start Date chronologically.

Answer 5

Ok that works. However now this begs for another question. What should one do if it is desired to have multiple validations on different columns but you wish to have a different User message: per validation failure?

Found the answer here

https://social.technet.microsoft.com/Forums/sharepoint/en-US/830cd2d8-3dac-4f23-8191-f6cf76ec65e0/multiple-list-validations-is-it-possible

A bit of a let down in my opinion because it leaves much to be desired to inform teh user what is wrong with the entries. Apart from this different one might need different validations depending on levels of access. For example teh normal user cannot enter a past date however and administrator might be allowed to do so.