Whilst working with Excel, you may need to check if a cell contains dates and return a value accordingly. Now, wouldn’t it be great to have Excel functions to do the hard work for you? Keeping this in mind, this article describes **5** easy ways to check** if cell contains date then return value in Excel**. Moreover, we’ll explore if cell contains date and then apply **Conditional Formatting**.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the link below.

## 5 Examples to Return Value in Excel If Cell Contains Date

Let’s consider the **Interview Schedule** dataset in the **B4:C13 **cells. Here, we have the **Names** of the employees and their scheduled **Interview Dates** respectively. Now, we want to determine whether the candidates have been interviewed or not. To do this, we’ll employ some widely used Excel functions in the following methods. Therefore, let us see each method in detail.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience.

__Method-1__: Using DATEVALUE Function to Return Value If Cell Contains Date

Let’s start with the most obvious and simplest way to check if a cell contains a date and returns a value. Here, we’ll use the **DATEVALUE function** to return the date in a text format. So, just along.

📌 ** Steps**:

- First of all, go to the
**D5**cell and enter the formula given below.

`=IF(C5<DATEVALUE("28-8-2022"),"Yes","No")`

Here, the **C5 **cell refers to the *Interview Date 01-01-22 *while we’ve hard coded today’s date which is *28-08-22*.

**Formula Breakdown:**

**DATEVALUE(“28-8-2022”)**→ converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code. Here,**“28-8-2022”**is theargument which is today’s date and Excel returns the date in text format.*date_text***Output → 44801**

**IF(C5<DATEVALUE(“28-8-2022″),”Yes”,”No”) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**C5<DATEVALUE(“28-8-2022”)**is theargument that*logical_test***compares the date**in the**C5**cell with today’s date. If this value is less than today’s date then the function returns “**Yes”**(argument) otherwise it returns*value_if_true***“No”**(argument).*value_if_false***Output → Yes**

- Then, use the
**Fill Handle Tool**to copy the formula into the cells below.

Finally, your result should look like the image shown below.

**Read More: ****Excel Formula If One Date is Greater Than Another Date**

__Method-2__: Returning Value with DATE Function If Cell Contains Date

Another way to check if a cell contains a date and returns a value involves using the **DATE function** which returns the date-time format. It’s simple and easy so just follow these steps.

📌 ** Steps**:

- First and foremost, move to the
**D5**cell and type in the expression given below.

`=IF(C5<DATE(2022,8,28), "Yes","No")`

In this formula, the **C5** cell represents the *Interview Date 01-01-22.*

**Formula Breakdown:**

**DATE(2022,8,28)**→ returns the number that represents the date in Microsoft Excel date-time code. Here,**2022**is theargument, next*year***8**is theargument, and*month***28**is theargument.*day***Output → 28-08-22**

**IF(C5<DATE(2022,8,28), “Yes”,”No”) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**C5<DATE(2022,8,28)**is theargument that compares the date in the*logical_test***C5**cell with the given date. If this value is less than the given date then the function returns “**Yes”**(argument) otherwise it returns*value_if_true***“No”**(argument).*value_if_false***Output → Yes**

After completing the above step your output should look like the picture shown below.

**Read More: ****Excel Formula If Date Is Less Than Today (4 Examples)**

**Similar Readings**

**Excel Formula If Date Is Greater Than 2 Years (3 Examples)****Conditional Formatting Based on Date Older Than 1 Year in Excel****Excel Conditional Formatting for Date Within 3 Months (3 Methods)****How to Compare Dates in Two Columns in Excel (8 Methods)**

__Method-3__: Utilizing TODAY Function to Return Value If Cell Contains Date

So far what we’ve done is nice, but there is a major problem. Simply put, the date is hard-coded into the formula, so we need to enter it manually every time. Now, to solve this issue we can use the **TODAY function** which gives today’s date. So, let us go through the steps if cell contains date then return value in Excel.

📌 ** Steps**:

- In the first place, jump to the
**D5**cell and enter the following formula.

`=IF(C5<TODAY(), "Yes","No")`

In this expression, the **C5** cell points to the *Interview Date 01-01-22.*

**Formula Breakdown:**

**TODAY()**→ returns the current date formatte as a date.**Output → 28-08-22**

**IF(C5<TODAY(), “Yes”,”No”) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**C5<TODAY()**is theargument that compares the date in the*logical_test***C5**cell with today’s date. If this value is less than the today’s date then the function returns “**Yes”**(argument) otherwise it returns*value_if_true***“No”**(argument).*value_if_false***Output → Yes**

Eventually, your output should look like the screenshot given below.

**Read More: ****How to Compare Dates to Today with Excel VBA (3 Easy Ways)**

__Method-4__: Combining IF and AND Functions to Return Value Between Two Dates

Now, what if you want to check if a cell contains date that returns a value between two dates? Suppose we have the **Start Date **at **01–01-22 **and the **End Date **at **01-09-22 **and we want to know which interviews have been completed and which are still outstanding. Here, we can combine the **IF** and **AND** functions to achieve the results.

📌 ** Steps**:

- To begin with, navigate to the
**D5**cell and type in the formula provided below.

`=IF(AND(C5>=$G$4,C5<=$G$5), "Yes", "No")`

Here, the **G4 **and **G5** cells point to the *Start Date (01–01-22) *and the *End Date (01–09-22)* respectively.

📃 *Note: **Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

**Formula Breakdown:**

**AND(C5>=$G$4,C5<=$G$5) →**checks whether all the arguments are**TRUE,**and returns**TRUE**if all the arguments are**TRUE**. Here,**C5>=$G$4**is theargument and*logical1***C5<=$G$5**is theargument since both arguments are*logical2***TRUE**so**the AND function**returns the output**TRUE**.**Output → TRUE**

**IF(AND(C5>=$G$4,C5<=$G$5), “Yes”, “No”) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**AND(C5>=$G$4,C5<=$G$5)**is theargument that compares if the date in the*logical_test***C5**cell is greater than equal to the**G4**cell and if the date in**C5**is less than equal to the**G5**cell. If this statement is**TRUE**, then the function returns “**Yes”**(argument) otherwise it returns*value_if_true***“No”**(argument).*value_if_false***Output → Yes**

Finally, the results should appear in the image shown below.

**Read More: ****How to Compare If Date Is Before Another Date in Excel**

__Method-5__: Applying COUNTIFS Function to Return Value If Cell Contains Specific Date

If you want to return a value to check for a specific date you can use the **COUNTIFS** **function**. So, let’s see the process in detail.

Assuming the **Employee Birthdays **dataset shown in the **B4:C13 **cells. Here, the dataset shows the employee **Names **and their **Birthdays **respectively.

📌 ** Steps**:

- Firstly, select the
**C4:C13**cells >> double-click the**Name Box**>> enter a suitable name for this range of cells. In this case, we’ve named it**birthdays**.

- Next, proceed to the
**G5**cell and enter the formula given below.

`=IF(COUNTIFS(birthdays,F5)>0, "Yes", "No")`

Here, the **F5** cell refers to the *Birthday of Anna*.

**Formula Breakdown:**

**COUNTIFS(birthdays,F5) →**counts the number of cells specified by a given set of conditions or criteria. Here,**birthdays**is theargument and*criteria_range1***F5**is theargument since the date in the*criteria1***F5**cell is present in the*birthdays*range so the function returns**1**.**Output → 1**

**IF(COUNTIFS(birthdays,F5)>0, “Yes”, “No”) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**COUNTIFS(birthdays,F5)>0**is theargument which returns*logical_test***1**. Since**1 > 0,**so the logical test is**TRUE**and the function returns “**Yes”**(argument) otherwise it returns*value_if_true***“No”**(argument).*value_if_false***Output → Yes**

- Then, copy the same formula to the cell below. Now, the
*birthday of Emily*is not present in the original list so the function returns a**No**.

Consequently, the output should look like the picture shown below.

**Read More: ****Conditional Formatting for Dates Older Than Certain Date in Excel**

## If Cell Contains Date Then Apply Conditional Formatting

Last but not least, we’ll highlight the cells if cell contains date using Excel’s **Conditional Formatting** feature. On this occasion, let’s consider the **Project Task List **dataset shown in the **B4:C13** cells which shows the **Task** and its **Date** of performance. Specifically, we want to highlight those *Dates* which coincide with the **Holiday Dates**. Hence, let’s glance at this method with the appropriate illustrations.

📌 ** Steps**:

- At the very beginning, select the
**C5:C13**cells >> go to the**Conditional Formatting**drop-down >> choose the**New Rule**option.

In an instant, the **New Formatting Rule **wizard pops up.

- Next, choose the
**Use a formula to determine which cells to format**option. - Then, in the
**Rule Description**enter the following formula.

`=ISNUMBER(INDEX($C$5:$C$13,MATCH(C5,$C$16:$C$24,0)))`

Here, the **C5** cell points to the *Project Approval Date *while the **$C$5:$C$13** and **$C$16:$C$24** ranges represent the *Dates *for the *Task* and *Holidays*.

**Formula Breakdown:**

**MATCH(C5,$C$16:$C$24,0)) →**the**MATCH function**returns the relative position of an item in an array matching the given value. Here,**C5**is theargument that refers to the*lookup_value**Project Approval Date*. Following,**$C$16:$C$24**represents theargument from where the value is matched. Lastly,*lookup_array***0**is the optionalargument which indicates the*match_type***Exact match**criteria.**Output → #N/A**

**INDEX($C$5:$C$13,MATCH(C5,$C$16:$C$24,0)) →**becomes**INDEX($C$5:$C$13,#N/A) →**the**INDEX function**returns a value at the intersection of a row and column in a given range. In this expression, the**$C$5:$C$13**is theargument which are the*array**Task Dates*. Next,**#N/A**is theargument that indicates that the*row_num**Date*is not present.**Output → #N/A**

**ISNUMBER(INDEX($C$5:$C$13,MATCH(C5,$C$16:$C$24,0))) →**becomes**ISNUMBER(#N/A) →**the**ISNUMBER function**checks whether a value is a number and returns**TRUE**or**FALSE**. Here,**#N/A**is theargument, and since it is not a number so the function returns*value***FALSE**.**Output → FALSE**

📃 *Note: **Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

Now, you can see the entire procedure in the GIF below.

Lastly, this should highlight the *Holidays* in bright orange color.

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

## Conclusion

I hope all the methods shown above to check **if cell contains date then return value in Excel,** will prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.