Using Logical Functions to Test Date Conditions in Excel

Sometimes, you may want to check if a date in one column of an Excel table is within the range of dates in another column of a different table. For example, you may want to see if a receipt date falls within a leave period, or if a delivery date is within a contract term.

To do this, you can use the IF function with the AND function. The IF function tests a condition and returns one value if the condition is true, and another value if the condition is false. The AND function returns true if all the arguments are true, and false otherwise.

The basic formula to check if a date is within a range of dates is:

=IF(AND(date>=start_date,date<=end_date),"Yes","No")

This formula returns “Yes” if the date is between the start date and the end date, inclusive, and “No” otherwise.

Procedures

To apply this formula to check if dates in one set are within the bounds of another set in Excel, follow these steps:

1. Arrange your data in two tables, one with the dates you want to check, and another with the start and end dates of the ranges. Make sure the dates are formatted as dates in Excel, and not as text.
2. In the table with the dates you want to check, enter the formula in an empty column next to the date column. Replace the date, start_date, and end_date arguments with the appropriate cell references. For example, if your date is in cell A2, and your start and end dates are in cells B2 and C2, respectively, enter this formula in cell D2:

=IF(AND(A2>=B2,A2<=C2),"Yes","No")

3. Copy the formula down to the rest of the rows in the table. You will see “Yes” or “No” in the column, indicating whether the date is within the range or not.
4. If you have multiple ranges of dates in the other table, you can use the OR function to check if the date is within any of the ranges. The OR function returns true if any of the arguments are true, and false otherwise. The formula to check if a date is within any of the ranges is:

=IF(OR(AND(date>=start_date1,date<=end_date1),AND(date>=start_date2,date<=end_date2),...),"Yes","No")

This formula returns “Yes” if the date is between any of the start and end dates, and “No” otherwise. You can add as many AND functions as you need, depending on how many ranges you have.

Example

Suppose you have the following data in Excel:

You want to check if the receipt dates in the first table are within the leave periods in the second table. To do this, you can use the following formula in cell C2 of the first table, and copy it down:

=IF(OR(AND(B2>=VLOOKUP(A2,Sheet2!$A$2:$C$6,2,FALSE),B2<=VLOOKUP(A2,Sheet2!$A$2:$C$6,3,FALSE)),AND(B2>=VLOOKUP(A2,Sheet2!$A$2:$C$6,2,TRUE),B2<=VLOOKUP(A2,Sheet2!$A$2:$C$6,3,TRUE))),"Yes","No")

This formula uses the VLOOKUP function to find the start and end dates for each name in the second table, and then uses the OR and AND functions to check if the date is within any of the ranges. The result is: