Excel: A Formula for “If Date is Before Another Date”


You can use one of the following formulas in Excel to check if one date is before another date:

Method 1: Use Cell Reference

=IF(B2<$B$14, "Yes", "No")

This particular formula checks if the date in cell B2 is less than the date in cell B14 and returns “Yes” or “No” accordingly.

Method 2: Use Actual Date

=IF(B2<DATEVALUE("2/20/2023"), "Yes", "No")

This particular formula checks if the date in cell B2 is less than 2/20/2023 and returns “Yes” or “No” accordingly.

The following example shows how to use each method in practice.

Example: Check if Date is Before Another Date in Excel

Suppose we have the following list of tasks in Excel along with their completion date and we would like to know if each task was completed by the date specified in cell B14 or not:

We can type the following formula into cell C2:

=IF(B2<$B$14, "Yes", "No")

We can then click and drag this formula down to each remaining cell in column C:

Column C returns either “Yes” or “No” depending on whether or not the corresponding date in column B is less than 2/20/2023.

If we’d like, we could also directly type the date into the IF function by wrapping it in the DATEVALUE function so that Excel recognizes it as a date:

=IF(B2<DATEVALUE("2/20/2023"), "Yes", "No")

We can enter this formula into cell C2 and click and drag it down to each remaining cell in column C:

Excel formula for IF date less than another date

Column C returns either “Yes” or “No” to indicate whether or not the corresponding date in column B is less than 2/20/2023.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Calculate Age on a Specific Date
Excel: How to Check if Date is Between Two Dates
Excel: How to Use VLOOKUP by Date

One Reply to “Excel: A Formula for “If Date is Before Another Date””

  1. Hi Zach,
    Thanks for this article, it’s really helpful.
    When implementing the formula =IF(B2<$B$14, "Yes", "No"), I would like to copy it into the next row, but the autofill feature only converts the first column name (B2 in your example), but it doesn't modify the second column number (14). Therefore, =IF(B2<$B$14, "Yes", "No") should be =IF(B3<$B$15, "Yes", "No") in the next row, but the B$14 remains the same. Do you know some way to autofill these numbers properly?
    Thank you so much!

Leave a Reply

Your email address will not be published. Required fields are marked *