Using If Function to Compare Dates of Two Cells

In this article, we will compare dates using the IF function in Excel 2016. IF function works on the logic test and returns the output on the basis of the test.

IF function tests the condition and returns value either it’s True or False.
Syntax of IF function:

=IF(Logic_test,[value_if_true],[Value_if_false])

Here is an example to show how to compare dates in excel. How do we check if  dates are greater than or equal to, does not equal to, less than, etc in excel

We have two lists named Date Column 1 and Date Column 2. We will compare the two lists using the IF function.

Compare date 1

Now we will use the function IF in C2 cell
Formula:

=IF( A2<B2 , “No” , “Yes” )

A2<B2  logic_test to compare dates and returns the values corresponding to it.
“No” value returned if the condition comes True
“Yes” value returned if the condition comes False

Compare date 2

Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl+ D.

Your Formula will be pasted using the shortcut and the resulting output will be like shown below.

Compare date 3

If Dates in Column 2 is greater than Dates in Column 1, No is the response or else then Yes is the response as shown in the above image.

Here was an example to show one of the features of the IF function in Excel 2016. Please find more Logic_test function here. If you have any query on IF function, please do share in the comment box below. We will help you.

Users are saying about us...

  1. I typed the date as shown above starting in L2 and entered the formula in N2 “=IF(L2>M2,”NO”,”YES”)” returning the following results: NO NO YES NO YES YES YES NO YES, which is different than the example above. Why would this be?

    • You’re results are correct with one exception. There is an obvious error in the example given.

      You might want to check the data in row 7. 29-Jun-14 is obviously not greater than 26-Jul-14 so the IF result is false and the result should be NO whereas you got YES. All your other data matches my results.

        • Hi Bhavana,

          Since we know that Excel stores Date & Time as serial numbers where integers part represent the dates and decimal portions represent the time part. So, even if there is time included in any of the criteria, the function will check for the criteria value you will be entering, and will return accordingly.

          In case if one criteria does not contain any time value whereas the other contains, excel by default will assume midnight value (00:00:00) for cell which does not have time portion and will return the result accordingly.

          Happy Learning!
          Team Excel Tip

  2. I want to know the how many days I worked as mentioned dates in 4 work sheet. i.e I want to know how many days I worked in a month depending upon the different work hours mentioned in separate work sheets as dated.Please help me.

  3. hi I need to know how to compare three dates and see if they are all the same, also once the comparision is done, cells which are different need to be highlighted in red

    • Hi Ayeshah,

      Can you please confirm when you say “Cells which are different need to be highlighted in red”. Do you mean if 2 cells contain the same date and 1 cell contains the different date then 2 cells should be highlighted instead of 1 who is carrying different date? Please do confirm.

      Happy Learning,
      Site Admin

  4. in the example above, 7/9/2014 (A1) is being compared to A2 the roll below ! is that the intention ?? it looks like we should be comparing A2 and B2 !!!
    This is confusing…

  5. I’m trying to create formula where certain items are due at specific times over several days. I need the date to change once it turns over midnight. I’m assuming this is an IF THEN formula, but i can’t seem to make it happen. Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube