Eshrak Kader
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.
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 the date_text argument which is today’s date and Excel returns the date in text format.
- 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 the logical_test argument that 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” (value_if_true argument) otherwise it returns “No” (value_if_false argument).
- 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 the year argument, next 8 is the month argument, and 28 is the day argument.
- 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 the logical_test argument that compares the date in the C5 cell with the given date. If this value is less than the given date then the function returns “Yes” (value_if_true argument) otherwise it returns “No” (value_if_false argument).
- 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 the logical_test argument that compares the date in the C5 cell with today’s date. If this value is less than the today’s date then the function returns “Yes” (value_if_true argument) otherwise it returns “No” (value_if_false argument).
- 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 the logical1 argument and C5<=$G$5 is the logical2 argument since both arguments are 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 the logical_test argument that compares if the date in the 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” (value_if_true argument) otherwise it returns “No” (value_if_false argument).
- 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 the criteria_range1 argument and F5 is the criteria1 argument since the date in the 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 the logical_test argument which returns 1. Since 1 > 0, so the logical test is TRUE and the function returns “Yes” (value_if_true argument) otherwise it returns “No” (value_if_false argument).
- 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 the lookup_value argument that refers to the Project Approval Date. Following, $C$16:$C$24 represents the lookup_array argument from where the value is matched. Lastly, 0 is the optional match_type argument which indicates the 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 the array argument which are the Task Dates. Next, #N/A is the row_num argument that indicates that the 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 the value argument, and since it is not a number so the function returns 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.
