Updated June 12, 2023
Introduction to Compare Dates in Excel
Working as a Data Analyst who always has to deal with a huge amount of data, you might have faced situations where you need to compare two dates. Either you are interested in checking whether the dates match each other in two different columns or not, or you may want to check if one of the data values is lesser than or greater than the other. Sometimes, you may have different testing criteria for two date columns based on the requirement of analysis or need of the hour, we must say. Well, Compare Dates in Excel is a simple task and may not always require much effort (unless the criteria are different and more difficult than usual).
What is Compare Dates in Excel?
When you need to compare two date values present in two different columns for equality or check if dates from one column are greater or lesser than the other, such scenarios can be considered the ones you can call the date comparison scenarios. It also varies from situation to situation (we use the conditional IF statement). Let’s see some examples where we compare dates and get useful insights for a better realization of the topic.
Examples of Compare Dates in Excel
Lets us discuss Compare Dates in Excel with Some Examples.
Example #1 – Comparing if Two Date Values match or Not
Suppose we have data, as shown below, consisting of two columns with different date values. We need to check whether the two date values match each other or are not matching with each other. Follow the steps below to check whether each date value from the two columns matches.
Step 1: Initiate a formula under cell C2 by typing equals to sign as shown below:
Step 2: Since we wanted to check whether date values between cells A2 and B2 match, we can do it by using the simplest of all comparison operators named equals to. Use “A2 = B2” in cell C2 after the initial equals operator.
The formula in cell C2 checks whether the values in cells A2 and B2 match or not. If the values match, it will return the Boolean output as TRUE. If values are not matching, it will return a Boolean output as FALSE.
Step 3: The formula is complete. Press Enter key and drag the formula across the cells to see the output of the formula. You can drag the formula using Ctrl + D shortcut. However, you need to select all the cells in column C across C2 to C11.
You can see that for those cells where date values match each other, the system generates a Boolean output as “TRUE”; for those where date values do not match, it causes the output as “FALSE”.
If you would have noticed, cell C gives the output as TRUE. Even if the values from cells A2 and B2 don’t match the naked eye, both values are the same. This is just a difference in format. The date values are stored some days from the seed date 01-01-1900 under Excel. Thus, 43943 is nothing but a difference in the number of days for the date 22-04-2020 from the seed date. That’s the reason they are matching with each other.
Example #2 – Comparing if Dates are Greater than or Less than the Other Dates
Now, we will move further and check whether the date values from one column are greater than or less than the date values from another column. This can be done with the help of Greater Than (“>”) and Less Than (“<“) operators in Excel. Follow the steps below to get an idea of how it works.
To check if dates from one column are greater than the dates from another column:
Step 1: In cell C2, type formula as” =A2>B2”. As the formula speaks, this will check if the date value under cell A2 is greater than the date value under cell B2. If it is, the system will generate a Boolean output as TRUE. If it is not, then the system will generate a Boolean output as FALSE.
Drag the formula across rows to get the output for all cells in column C.
Similarly, you can check whether column A’s date values are lesser than column B’s. All you need to do is replace the Greater Than (“>”) operator with Lesser Than (“<“) operator in the formula used in the previous step. See the screenshot below under cell D2.
Drag the formula across cells in column D to get the desired output.
Example #3 – Customizing Date Comparing using Conditional IF Statement
Getting TRUE or FALSE as values seems a bit weird to the naked eye, as someone who looks at your data may not know what TRUE and FALSE resemble inside respective columns. We can customize the output of our comparison by using the Conditional IF Statement, which is a part of Excel.
Step 1: Initiate the conditional IF statement as shown in the screenshot below:
IF statement first checks if the given logical condition generates output as TRUE and FALSE. Based on the output, it prints the result specified under value_if_true and value_if_false arguments.
Step 2: We need to check whether two date values from columns A and B match; we can use A2=B2 as a logical_test argument under the IF statement.
Step 3: Now, we need to specify the argument if the condition holds or is TRUE or, in other words, two date values are matching with each other. We will use “Matching” as a value for argument value_if_true.
Step 4: Similarly, for the value_if_false argument, we will use “Not Matching”, which will represent those cells where the date value from two columns does not match each other.
Now, Press Enter to see the output as shown below:
Since the date values present under cells A2 and B2 match each other, we get output as “Matching” under cell C2.
Step 5: Drag the formula to get the output for all the cells.
These are some comparison methods for comparing data values across two columns under Excel. We will allow this article to end here. But have some points for you to remember:
Things to Remember
- Date values are stored as the number of days from the seed date at the system’s backend. Don’t get confused if you see a date value and a number value match each other in some cases. Ex. Row 8 in our example.
- The date values stored under quotation marks are considered as text/string under Excel, and they will not be the same as the actual date values, even if they look the same. See the example screenshot below:
Recommended Articles
This has been a guide to Compare Dates in Excel. Here we discuss How to Compare Dates in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
