How to Use the IF Function to Check Whether a Date is in the Past or the Future in Excel

The IF function is one of the most commonly used functions in Excel. It allows you to perform a logical test and return one value if the test is true, and another value if the test is false. The syntax of the IF function is:

=IF(logical_test, value_if_true, value_if_false)

Where:

• logical_test is any expression that can be evaluated as true or false, such as A1>B1, C2=“Yes”, or D3<=100.
• value_if_true is the value that you want to return if the logical test is true. It can be a number, text, cell reference, formula, or another IF function.
• value_if_false is the value that you want to return if the logical test is false. It can be a number, text, cell reference, formula, or another IF function.

To check whether a date is in the past or the future, you can use the IF function with the TODAY function, which returns the current date. The TODAY function has no arguments and is written as:

=TODAY()

For example, if you want to check whether the date in cell A2 is in the past or the future, you can use the following formula:

=IF(A2<TODAY(), "Past", "Future")

This formula will return “Past” if the date in A2 is before the current date, and “Future” if the date in A2 is after the current date. If the date in A2 is equal to the current date, the formula will return “Future” as well.

Procedures

To use the IF function to check whether a date is in the past or the future in Excel, follow these steps:

1. Enter the dates that you want to check in a column, such as column A.
2. In another column, such as column B, enter the formula =IF(A2<TODAY(), "Past", "Future") and press Enter. This will check whether the date in A2 is in the past or the future and return the corresponding value.
3. Drag the fill handle down to copy the formula to the other cells in column B. This will check the dates in the rest of column A and return the corresponding values in column B.

Explanation

The IF function can be used to check whether a date is in the past or the future in Excel by comparing the date with the current date returned by the TODAY function. The IF function takes three arguments: a logical test, a value if true, and a value if false. The logical test can be any expression that can be evaluated as true or false, such as A2<TODAY(), which checks whether the date in A2 is before the current date. The value if true is the value that you want to return if the logical test is true, such as “Past”. The value if false is the value that you want to return if the logical test is false, such as “Future”. The IF function will return one of these values depending on the result of the logical test.

For example, suppose you have the following dates in column A:

To check whether these dates are in the past or the future, you can use the following formula in column B:

=IF(A2<TODAY(), "Past", "Future")

This formula will compare the date in A2 with the current date returned by the TODAY function, which is 2/6/2024 in this example. Since the date in A2 is before the current date, the logical test A2<TODAY() will be true, and the IF function will return “Past”. If you copy the formula to the other cells in column B, you will get the following results:

Scenario

To illustrate how to use the IF function to check whether a date is in the past or the future in Excel, let’s consider a scenario where you are planning a trip and you have a list of destinations and their departure dates. You want to check which destinations are still available and which ones are already past their departure dates. You can use the IF function to do this by following these steps:

1. Enter the destinations and their departure dates in columns A and B, respectively. For example:

2. In column C, enter the formula =IF(B2<TODAY(), "Past", "Available") and press Enter. This will check whether the departure date in B2 is in the past or the future and return the corresponding value.
3. Drag the fill handle down to copy the formula to the other cells in column C. This will check the departure dates in the rest of column B and return the corresponding values in column C.

Result

After applying the IF function to check whether the departure dates are in the past or the future, you will get the following results in column C:

As you can see, the IF function has returned “Past” for the destinations that have already passed their departure dates, and “Available” for the destinations that are still in the future. This way, you can easily see which destinations are still open for booking and which ones are not.

Other Approaches

Besides using the IF function, there are other ways to check whether a date is in the past or the future in Excel. Here are some of them:

• You can use conditional formatting to highlight the dates that are in the past or the future with different colors. To do this, select the dates in column B, go to the Home tab, click on Conditional Formatting, and choose Highlight Cells Rules > A Date Occurring. Then, select the options that you want, such as Yesterday, Tomorrow, Last Week, Next Week, etc. and choose the colors that you want. This will apply the conditional formatting to the dates based on their relation to the current date.
• You can use the DATEDIF function to calculate the number of days between the dates and the current date. The DATEDIF function returns the difference between two dates in various units, such as days, months, or years. The syntax of the DATEDIF function is:

=DATEDIF(start_date, end_date, unit)

Where:

• start_date is the earlier date, such as the departure date in column B.
• end_date is the later date, such as the current date returned by the TODAY function.
• unit is the unit of time that you want to return, such as “d” for days, “m” for months, or “y” for years.

For example, if you want to calculate the number of days between the departure date in B2 and the current date, you can use the following formula:

=DATEDIF(B2, TODAY(), "d")

This formula will return the number of days between the two dates. If the result is positive, it means the date is in the future. If the result is negative, it means the date is in the past. If the result is zero, it means the date is equal to the current date. You can copy the formula to the other cells in column C to get the results for the other dates. For example:

As you can see, the DATEDIF function has returned the number of days left until the departure dates. You can use this information to plan your trip accordingly.