SOLVED

Sharepoint calculated column if date greater than another column

%3CLINGO-SUB%20id%3D%22lingo-sub-282591%22%20slang%3D%22en-US%22%3ESharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282591%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20fields%20on%20my%20Sharepoint%20list.%20One%20of%20them%20is%20called%20%3CSTRONG%3E'From%20Date'%3C%2FSTRONG%3E%20and%20another%20one%20called%20%3CSTRONG%3E'To%20Date'%3C%2FSTRONG%3E.%26nbsp%3BHow%20do%20I%20create%20a%20formula%20that%20validates%20if%20%3CSTRONG%3ETo%20Date%20is%20greater%20than%20From%20Date%3C%2FSTRONG%3E%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20this%20formula%20%3D%5BFrom%20Date%5D%26lt%3B%5BTo%20Date%5D%20but%20it%20didn't%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20glad%20if%20someone%20could%20help%20me%20with%20this!%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283283%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283283%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F131055%22%20target%3D%22_blank%22%3E%40Rafael%20Benicio%3C%2FA%3E%2C%20yes%20that%20is%20how%20the%20formula%20would%20be%20for%20list%20validation.%20Apologies%2C%20the%20original%20formula%20I%20gave%20you%20was%20for%20a%20calculated%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283168%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283168%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F177418%22%20target%3D%22_blank%22%3E%40Matt%20Weston%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20it%20was%20working%20but%20it's%20not!%20Just%20noticed%2C%20the%20validation%20is%20not%20working%20properly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20that%20works%20is%20this%20one%26nbsp%3B%3D%5BTo%20Date%5D%26gt%3B%3D%5BFrom%20Date%5D.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-283071%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283071%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F177418%22%20target%3D%22_blank%22%3E%40Matt%20Weston%3C%2FA%3E%26nbsp%3BNever%20mind%2C%20I%20put%20the%20formula%20in%20the%20validation%20settings%20under%20list%20settings%20and%20it%20worked.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%20Cheers!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282908%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282908%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F177418%22%20target%3D%22_blank%22%3E%40Matt%20Weston%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%20Unfortunately%2C%20The%20formula%20below%20didn't%26nbsp%3Bwork.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(%5BTo%20Date%5D%26gt%3B%5BFrom%20Date%5D%2C%22Date%20Greater%20Than%22%2C%22Date%20Less%20Than%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20error%20message%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59154iF33B8E73EA29FDA7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202018-11-06%20at%208.03.32%20AM.png%22%20title%3D%22Screen%20Shot%202018-11-06%20at%208.03.32%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-282752%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-282752%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F131055%22%20target%3D%22_blank%22%3E%40Rafael%20Benicio%3C%2FA%3E%26nbsp%3Bif%20you're%20looking%20at%20doing%20something%20with%20a%20calculated%20column%20then%20you%20could%20use%20a%20formula%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIF(%5BEnd%20Date%5D%26gt%3B%5BStart%20Date%5D%2C%22Date%20Greater%20Than%22%2C%22Date%20Less%20Than%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20apply%20column%20formatting%20to%20this%20column%20to%20then%20show%20a%20visible%20difference%20between%20the%20TRUE%20and%20FALSE%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20looking%20to%20do%20column%20level%20validation%2C%20then%20unfortunately%20you%20can't%20compare%20two%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419936%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419936%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F177418%22%20target%3D%22_blank%22%3E%40Matt%20Weston%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20getting%20an%20error%20message%20for%20the%20same%20formula%20(different%20column%20names)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%5BEnd%20Date%20of%20Action%5D%26gt%3B%3D%5BStart%20Date%20of%20Action%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EError%3A%3CBR%20%2F%3ESorry%2C%20something%20went%20wrong%3CBR%20%2F%3EThe%20formula%20cannot%20refer%20to%20another%20column.%20Check%20the%20formula%20for%20spelling%20mistakes%20or%20update%20the%20formula%20to%20reference%20only%20this%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20a%20suggestion%20as%20to%20what%20the%20solution%20is%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419962%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20calculated%20column%20if%20date%20greater%20than%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419962%22%20slang%3D%22en-US%22%3EFixed%20my%20error%20-%20this%20has%20to%20be%20a%20list%20validation%2C%20not%20a%20column%20validation!%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsocial.msdn.microsoft.com%2FForums%2Fsharepoint%2Fen-US%2F4e4659a7-fced-47e3-8005-2943d390099e%2Fsharepoint-2013-the-formula-cannot-refer-to-another-column%3Fforum%3Dsharepointdevelopment%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsocial.msdn.microsoft.com%2FForums%2Fsharepoint%2Fen-US%2F4e4659a7-fced-47e3-8005-2943d390099e%2Fsharepoint-2013-the-formula-cannot-refer-to-another-column%3Fforum%3Dsharepointdevelopment%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi guys, 

 

I have two fields on my Sharepoint list. One of them is called 'From Date' and another one called 'To Date'. How do I create a formula that validates if To Date is greater than From Date

 

I have tried this formula =[From Date]<[To Date] but it didn't work. 

 

I would be glad if someone could help me with this! Thanks!

7 Replies
Highlighted

Hi @Rafael Benicio if you're looking at doing something with a calculated column then you could use a formula like this: 

 

IF([End Date]>[Start Date],"Date Greater Than","Date Less Than")

 

You could apply column formatting to this column to then show a visible difference between the TRUE and FALSE values.

 

If you're looking to do column level validation, then unfortunately you can't compare two columns.

Highlighted

Hi @Matt Weston

 

Thank you for your reply. Unfortunately, The formula below didn't work. 

 

IF([To Date]>[From Date],"Date Greater Than","Date Less Than")

 

Here is the error message:

 

Screen Shot 2018-11-06 at 8.03.32 AM.png

Highlighted

@Matt Weston Never mind, I put the formula in the validation settings under list settings and it worked. 

 

Thank you so much! Cheers! 

Highlighted
Best Response confirmed by Rafael Benicio (Contributor)
Solution

Hey @Matt Weston

 

I thought it was working but it's not! Just noticed, the validation is not working properly. 

 

The formula that works is this one =[To Date]>=[From Date]. 

Highlighted

Hi @Rafael Benicio, yes that is how the formula would be for list validation. Apologies, the original formula I gave you was for a calculated column.

Highlighted

Hi @Matt Weston 

 

I am getting an error message for the same formula (different column names)

 

=[End Date of Action]>=[Start Date of Action]

 

Error:
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.

 

Do you have a suggestion as to what the solution is?

 

Many thanks!

Highlighted