Excel IF Function With Dates

If you have a list of dates and then want to compare to these dates with a specified date to check if those dates is greater than or less than that specified date. You can use the IF function in combination with logical operators and DATEVALUE function in Microsoft excel.

Excel IF function combining with DATEVALUE function

Since that Excel cannot recognize the date formats and just interprets them as a text string. So you need to use DATAVALUE function and let Excel think that it is a Date value. For example: DATEVALUE(“11/3/2018”). Now we can write down the following IF formula with Dates.


Excel IF Function With Dates1

The above excel IF formula will check the date value in Cell B1 if it is less than another specified date(11/3/2018), if the test is TRUE, then return “good”, otherwise return “bad”

Excel IF function combining with DATE function

You can also use DATE function in an Excel IF statement to compare dates, like the below IF formula:


Excel IF Function With Dates2

The above IF formula will check if the value in cell B1 is less than or equal to 11/3/2018 and show the returned value in cell C1, Otherwise show nothing.

Excel IF function combining with TODAY function

If you want to compare the current date with the specified date in the past, you can use IF function in combination with TODAY function in Excel. Like the following IF formula:

=IF(B1>TODAY(), “good”,”bad”)

Excel IF Function With Dates3

We also can use the complex logical test using Today function, like this: B1-TODAY>10, it will check the date value in one cell if it is more than 10 days from now. Let’s combine this logical test in the IF formula as follow:


Excel IF Function With Dates4

Related Formulas

  • Excel IF formula with operator : greater than,less than
    Now we can use the above logical test with greater than operator. Let’s see the below generic if formula with greater than operator:=IF(A1>10,”excelhow.net”,”google”) …
  • Excel IF function with text values
    If you want to write an IF formula for text values in combining with the below two logical operators in excel, such as: “equal to” or “not equal to”…
  • Excel IF Function With Numbers
    If you want to check if a cell values is between two values or checking for the range of numbers or multiple values in cells, at this time, we need to use AND or OR logical function in combination with the logical operator and IF function…

 Related Functions

  • Excel Date function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day) …
  • Excel IF function 
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE….




Related Posts

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Cell Contains Number

This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Add Row Numbers And Skip Blanks in Excel

Do you ever have to input a list of numbers into a spreadsheet, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have to scroll up ...