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:
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
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!
Correct me if I’m wrong here, but this only works if the year is the same, or if your date is formatted with the year first, because it isn’t actually telling if the date is greater or lesser, it’s comparing the raw number.
So while 04012024 is certainly less than 12012024, to the formula 04012024 is greater than 01012025, even though it comes before it on the calendar.
Hi Logan…You’re correct in thinking that date comparisons in Excel rely on the underlying numeric values of the dates. However, Excel stores dates as serial numbers (where January 1, 1900, is 1, and each subsequent day increments by 1), which allows for accurate date comparisons regardless of how the date is formatted or the year.
Here’s how it works:
1. **Date Format**: Excel dates are represented as serial numbers. For instance, `01-Jan-2024` is stored as `45152`, and `01-Jan-2025` is stored as `45517`. This ensures that comparisons between dates are based on these serial values, not the text representations.
2. **Date Comparison**: When you compare two dates using formulas like `=A1