Excel Compare Dates (Table of Contents)
Introduction to Compare Dates in Excel
Working as a Data Analyst who always have 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 are matching with each other in two different columns or not or you maybe wanted to check if one of the date values is lesser than or greater than the other. Also, 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, comparing two dates in Excel is a simple task and may not always require a lot of effort (unless and otherwise, the criteria are different as well as 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 you need to check if dates from one column are greater or lesser than the other, such scenarios can be considered as the ones which you can call the date comparison scenarios. It also varies situation to situation (for which we use the conditional IF statement). Let’s see some of the examples where we make a comparison between dates and get useful insights for the 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 are Matching or Not
Suppose we have data as shown below which consists of two columns that have different date values. We need to check whether the two date values are matching with each other or they are not matching with each other. Follow the steps below to check whether each date value from two columns is matching with each other or not.
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 cell A2 and B2 are matching or not, we can do it by using the simplest of all comparison operators named equals to. In use “A2 = B2” in cell C2 after the initial equals operator.
4.9 (2,356 ratings)
View Course
The formula in cell C2 checks if the values in cell A2 and B2 are matching with each other or not. If the values are matching, it will return 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 formula using Ctrl + D shortcut. However, for that, you need to select all the cells in column C across C2 to C11.
You can see for those cells, where date values are matching with each other, the system generates a Boolean output as “TRUE” and for the cells where date values are not matching, it generates the output as “FALSE”.
If you would have noticed, cell C gives the output as TRUE. Even if the values from cell A2 and B2 doesn’t seem to be matching to naked eyes. Actually, both of the values are the same. This is just a difference in format. The date values are being stored in a number of days from seed date 01-01-1900 under Excel. Thus, 43943 is nothing but a difference in a number of days for date 22-04-2020 from 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 a step further and check whether 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.
For checking if dates from one column are greater than the dates from another column:
Step 1: In cell C2, type formula as” =A2>B2”. Well as the formula itself speaks, this will check if the date value under cell A2 is greater than the date value under cell B2 or not. 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 date values from column A are lesser than those of column B. 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
Well, getting TRUE or FALSE as values seems a bit weird to naked eyes, as someone who looks at your data may not have an idea what TRUE and FALSE resembles 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 is generating 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 are matching or not, for this, 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 value_if_false argument, we will use “Not Matching” which will represent those cells where the date value from two columns is not matching with each other.
Now, Press Enter to see the output as shown below:
Since the date values present under cell A2 and B2 are matching with each other, we are getting 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 date 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 nothing but the number of days from seed date at the backend of the system. So, don’t get confused if you see a date value and number value are matching with 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 along with practical examples and downloadable excel template. You can also go through our other suggested articles –
