Excel Formula to Return a Date Conditionally

In this article, I will show you how to use a formula to return the date only when that date actually occurs in Excel. This can be useful if you want to display a date only on a certain condition, such as when it is today’s date or when it is a Monday.

The basic theory behind this formula is to use the IF function to check if a date meets a certain condition, and then return that date or a blank value depending on the result. The IF function has the following syntax:

=IF (logical_test, value_if_true, value_if_false)

Where:

• logical_test is the condition that you want to check.
• value_if_true is the value that you want to return if the condition is true.
• value_if_false is the value that you want to return if the condition is false.

For example, the following formula checks if the date in cell A1 is equal to today’s date, and returns the date or a blank value accordingly:

=IF (A1 = TODAY (), A1, “”)

Procedures

To use this formula, you need to follow these steps:

1. Enter the date values that you want to check in a column, such as column A.
2. In another column, such as column B, enter the formula with the IF function and the condition that you want to apply. For example, if you want to return the date only when it is a Monday, you can use the WEEKDAY function to check the day of the week, and use 2 as the second argument to indicate that the week starts on Monday and ends on Sunday. The formula would look like this:

=IF (AND (A1 = TODAY (), WEEKDAY (A1, 2) = 1), A1, “”)

3. Copy the formula down to the other cells in the column.
4. Format the cells with the formula as date format, if needed.

Explanation

The formula works by combining two conditions with the AND function, which returns TRUE only if both conditions are true, and FALSE otherwise. The two conditions are:

• A1 = TODAY (): This checks if the date in cell A1 is equal to today’s date, using the TODAY function that returns the current date.
• WEEKDAY (A1, 2) = 1: This checks if the date in cell A1 is a Monday, using the WEEKDAY function that returns a number from 1 to 7 representing the day of the week, and using 2 as the second argument to specify that the week starts on Monday and ends on Sunday. The number 1 represents Monday in this case.

If both conditions are true, the formula returns the date in cell A1. If either or both conditions are false, the formula returns a blank value, which is represented by two double quotes (“”).

Example

Suppose you have a list of dates in column A, and you want to display the date only when it is today’s date and a Monday in column B. You can use the following formula in cell B1 and copy it down to the other cells in column B:

=IF (AND (A1 = TODAY (), WEEKDAY (A1, 2) = 1), A1, “”)

The following table shows the result of the formula, assuming that today’s date is 16-Jan-2024, which is a Monday.

As you can see, the formula returns the date only when it is 16-Jan-2024, which is today’s date and a Monday. For the other dates, the formula returns a blank value.

Other approaches

There are other ways to achieve the same result with different formulas. Here are some examples:

• You can use the DATE function to create a date value from the year, month, and day arguments, and compare it with the date in cell A1. For example, the following formula returns the date only when it is 16-Jan-2024:

=IF (A1 = DATE (2024, 1, 16), A1, “”)

• You can use the TEXT function to convert the date in cell A1 to a text value in a specific format, and compare it with a text value that represents today’s date and a Monday. For example, the following formula returns the date only when it is today’s date and a Monday, using the format “d-mmm-yyyy ddd” that displays the day, month, year, and weekday:

=IF (TEXT (A1, “d-mmm-yyyy ddd”) = TEXT (TODAY (), “d-mmm-yyyy ddd”) & ” Mon”, A1, “”)

• You can use the EOMONTH function to return the last day of the month before or after a given date, and compare it with the date in cell A1. For example, the following formula returns the date only when it is the last day of the previous month:

=IF (A1 = EOMONTH (TODAY (), -1), A1, “”)

I hope this article has helped you understand how to use a formula to return the date only when that date actually occurs in Excel.