Using If Function to Compare Dates of Two Cells in Microsoft Excel

In this article we will learn how to use IF function to compare dates of two cells in Microsoft Excel 2010.

Before using the formula you should understand how the “IF” function works?

IF: -IF condition allowsthe user to use multiple conditions in a cell. It helps us to create the flexibility of the function in Microsoft Excel 2010 & Microsoft Excel 2013. Excel allows upto 64 conditions which can be checked in a cell through this function.

 

Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])

 

Let’s take an example and understand how you can use the IF function to compare dates in excel.

I have data in range A2:B10, in which column A contains the 1stset of Dates and column B contains the 2ndset of Dates. I would like to write an “IF” function which states that if the date in A2 is the same or earlier than the date in B2 than display “YES” and if not than display “NO”. To compare the dates in the 2 columns follow the below mentioned steps:-

 

image 3

 

  • Select the cell C2 and write the formula.
  • =IF (A1>A2,”NO”,”YES”) and press enter on the keyboard.
  • The function will return “Yes” or “NO”.

image 1

  • Copy the same formula by pressing the key “CTRL+C” and paste in to C3:C10 by pressing the key “CTRL+V” on the keyboard.

image 2

 

You can see in the above picture that the comparison has been performed and the function has returned a YES or NO.

To ensure that this function works correctly and gives you the right result each time, you need to ensure that both the columns are formatted in the same way. The date format should be the same so that it compares the correct dates and returns the right values.

 

image 48

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

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…

Leave a Reply to Bhavana Cancel 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