SharePoint Online Calculated Column AND Condition

Calculated columns in SharePoint are a special type of column. The role of the calculated column is to calculate the values based on the other columns using formulas in the same SharePoint list or library.

This article will show how to work with SharePoint Online Calculated Column AND Condition.

SharePoint Online Calculated Column AND Condition

The AND condition in the SharePoint calculated column determines if the two conditions are met. It provides one value if they are. It returns a different value otherwise.

Let’s start with examples on the SharePoint Online calculated column using the AND condition in the SharePoint list.

Look at the Training Records SharePoint list below.

SharePoint Online calculated column examples

Example 1: Overdue Training

Based on the “Training Date” and “Completion Status” columns, I will create a SharePoint calculated column to find which training topic takes more time than the given time.

To create a calculated column, follow the below steps:

1. In the SharePoint list -> Click on the Gear icon -> Go to List settings -> Under Columns, click on Create column

SharePoint calculated column and operator

2. Provide Column name -> Select column type [Calculated] -> Write a formula for the calculated column under the Formula box.

SharePoint Online calculated column and condition

3. Select the data type returned from the formula on the same page, then click OK.

SharePoint list calculated column and operator example

Here is the formula.

=IF(AND([Training Date]<TODAY(),[Completion Status]<>"Completed"),"Overdue", "Not Overdue")

This formula checks if the training date is before today and the completion status is not “Completed,” indicating that the training is overdue.

Look at the output image below.

examples of calculated column in SharePoint list

Follow the same steps to create a SharePoint calculated column. Below, I will show only the examples.

Example 2: Upcoming Trainings

I’m trying to find the upcoming training based on the Training Date column and Completion Status column using the SharePoint calculated column with AND condition.

Look at the formula below to achieve this requirement.

=IF(AND([Training Date]>TODAY(),[Completion Status]<>"Completed"),"Upcoming","Not Upcoming")

This formula checks if the training date is after today’s date and the completion status is not “Completed,” indicating that the training is upcoming. Now, look at the output image below.

examples of calculated column using AND condition in SharePoint list

Example 3: Critical Training

I need to find critical training based on the Training Topic and Completion Status columns in this example.

=IF(AND(OR([Training Topic]="Safety Procedures",[Training Topic]="First Aid"),[Completion Status]="Completed"),"Critical Training","Not Critical")

This formula checks if the training topic is “Safety Procedures” or “First Aid,” and the Completion status is “Completed,” indicating that the training is critical. For more clarification, have a look at the below image.

SharePoint calculated column using the AND condition

Example 4: Certification Expiry Reminder

I’m creating a SharePoint calculated column to remind about certification expiry in the “Training Record list” based on the Completion Status and Training Date columns. Look at the formula below.

=IF(AND([Completion Status]="Completed",[Training Date]<=TODAY()+30),"Certification Expires Soon","Certification Valid")

This formula checks if the completion status is “Completed” and the training date is within the next 30 days to remind that the certification will expire soon. Otherwise, it shows “Certification Valid.” refer to the output image below.

SharePoint online calculated column formulas using AND Condition.

So these are some examples of SharePoint Online Calculated columns using AND condition.

I hope you found this article useful. Use these simple examples, and then change them according to your usage.

Additionally, you may also like some more SharePoint articles: