How to Record a Value When Date Condition is Met in Excel Formula

Sometimes, you may want to record a value in a cell when a date condition is met. For example, you may want to track the date when a sales target is achieved, or when a project deadline is passed. In this article, I will show you how to use Excel formulas to accomplish this task.

To record a value when a date condition is met, you need to use two main functions: the IF function and the DATEVALUE function.

The IF function is used to test a condition and return one value if the condition is TRUE and another if it is FALSE. The syntax for the IF function is as follows:

=IF (logical_test, [value_if_true], [value_if_false])

The DATEVALUE function is used to convert a date in text format to a serial number that Excel can recognize as a date. The syntax for the DATEVALUE function is as follows:

=DATEVALUE (date_text)

By combining these two functions, you can create a formula that will record a value when a date condition is met. The general formula is:

=IF (date_cell operator DATEVALUE ("date_text"), value_if_true, value_if_false)

Here, date_cell is the cell reference that contains the date you want to compare, operator is one of the comparison operators (<, >, =, <=, >=), date_text is the date in text format that you want to compare with, value_if_true is the value that you want to record if the date condition is met, and value_if_false is the value that you want to record if the date condition is not met.

Example

Let’s say you have a table that shows the sales data of a company for each month in 2024. You want to record the date when the cumulative sales exceed $100,000. The table looks like this:

To record the date when the cumulative sales exceed $100,000, you can use the following formula in cell D2 and copy it down:

=IF (C2>100000,DATEVALUE (A2&"/1/2024"),"")

This formula will check if the value in column C is greater than 100,000. If yes, it will use the DATEVALUE function to convert the month name in column A to a date value, with the first day of the month and the year 2024. If no, it will return an empty string.

The result will look like this:

Other Approaches

There are other ways to record a value when a date condition is met in Excel formula. Here are some alternatives:

• You can use the IFERROR function to handle any errors that may occur when using the DATEVALUE function. For example, if the month name in column A is not valid, the DATEVALUE function will return an error. You can use the IFERROR function to return a blank or a custom message instead. The formula would be:

=IFERROR (IF (C2>100000,DATEVALUE (A2&"/1/2024"),""),"Invalid month")

• You can use the TEXT function to format the date value in the desired way. For example, if you want to display the date as mm/dd/yyyy, you can use the TEXT function to apply this format. The formula would be:

=IF (C2>100000,TEXT (DATEVALUE (A2&"/1/2024"),"mm/dd/yyyy"),"")

• You can use the EDATE function to calculate the date based on the month number instead of the month name. The EDATE function returns the date that is the specified number of months before or after a given date. For example, if you want to use the month number in column A as the input, you can use the EDATE function to get the date value. The formula would be:

=IF (C2>100000,EDATE ("1/1/2024",A2-1),"")

This formula will use the date “1/1/2024” as the start date, and add the month number in column A minus one as the number of months. For example, for May, the formula will return the date “5/1/2024”.