Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
1 like 0 dislike
24k views
in SharePoint Server by
edited by anonymous

In SharePoint 2019, I have two columns in a SharePoint list as below and I need to create a SharePoint calculated column to calculate IF the first column OR second column is >= TODAY().

  • Column1 Name , [Course1 Expiration Date] = Type Date
  • Column2 Name , [Course2 Expiration Date] = Type Date

My requirement is to have the calculated column Named [Status] to show if one of these Two-column dates above is equal or greater than TODAY DATE  it gives me Expired else it should show Valid, with Red and Green icon or colors if possible. 

Unfortunately, I  used the below formula but gave me an ERROR

 =IF(TODAY()=>[Course Expiration Date],[Course2 Expiration Date],"EXPIRED","VALID")

Please, help me to create a sharepoint calculated column formula with-OR condition that meets my requirements

Thanks


1 Answer

2 like 0 dislike
by 158 185 373

IF OR() in SharePoint Calculated Column

To use IF OR function in SharePoint Calculated Column, you should use the below formula syntax:

= IF(OR(Condition1,Condition2,....),TRUE,FALSE)

To apply the above IF OR syntax in your SharePoint Calculated Column, it should be

=IF(OR([Course1 Expiration Date]<=TODAY(),[Course2 Expiration Date]<=TODAY()),"Valid","Expired")

In this case, the SharePoint Calculated Column formula will only show the "Expired" value if the first date and second date are equal or greater than Today() as shown below.

How to use AND OR in SharePoint Calculated Column

But I don't think this SharePoint Calculated Column formula will meet your requirements where you need to show "Expired" if one of the dates columns is equal or greater than Today()! So in this case, you should use IF AND() instead of IF OR() to get the correct expected output.

IF AND() in SharePoint Calculated Column

To use IF AND function in SharePoint Calculated Column, you should use the below formula syntax:

= IF(AND(Condition1,Condition2,....),TRUE,FALSE)

To apply the above IF AND() syntax in your SharePoint Calculated Column, it should be

=IF(AND([Course1 Expiration Date]<=TODAY(),[Course2 Expiration Date]<=TODAY()),"Valid","Expired")

In this case, the SharePoint Calculated Column formula will show the "Expired" value if one of the date columns is equal or greater than Today() otherwise it will show "Valid" as shown below.
IF AND in SharePoint Calculated Column

The expected result for IF(OR()) and if(AND())

Below is a comparison between the expected result for IF(OR()) and IF(AND()) in SharePoint Calculated Column.
IF OR in SharePoint Calculated Column

Note: The Today() function will not preserve the first saved [Today] value, and it will be recalculated with the current date when the item is updated! Learn more at Supported and Unsupported Columns In SharePoint Calculated Column Formula

If you don’t ask, the answer is always NO!
...