Excel Compare Dates (Table of Contents)
Comparing Two Dates in Excel
Comparing two dates in excel could be different from one scenario to other, sometimes we may need to compare whether one date is equal to the other or not, sometimes we may need to check one date is above certain date or below certain date, and often times we may need to arrive based on testing all the mentioned criteria’s, like this it vary from scenario to scenarios. We will see each one them with practical examples.
How to Compare Two Dates in Excel?
Given below are some examples to compare two dates in excel.
#1 – Compare If Two Dates are Equal or Not
Comparing the date is equal to the other or not is simple, we will be having two dates and we need to check whether that cell date is equal to the other or not. Look at the below data to compare dates in excel.
- Now in cell C2 apply the formula as “=A2=B2”.
- Press Enter to get the result.
We have the same date in both columns so we got TRUE as a result.
- Drag the formula to the following columns as well to get the result.
So, wherever data is matched we have got the result as TRUE and wherever data is not matched we have got the result as FALSE.
One interesting fact about this example is row number 7, in this row first date is written as “29-Oct-2019” but the second date is written as 43767, even though both are different the result is still TRUE i.e. both are same.
The reason why I have told date & time are sensitive things in excel at the beginning because of this kind of scenario in excel. When we apply the date format “DD-MMM-YYYY” to serial number 43767 we will get the same result as A7 cell.
So, excel reads them as serial numbers, not as dates. Like this, we can compare two dates in excel.
Now assume you don’t want to see default results of TRUE or FALSE rather you may want to have a different result like “Both are Correct” if both the dates are matching or else you need a result like “Both are Not Correct”. So, this is done by using the logical function “IF”.
- Open IF function in the “MATCH” column.
- The first criteria of the formula is a logical test so in this case, our logical test is to check whether “Date 1” is equal to “Date 2” or not, so apply the logical test as E2 = F2.
- The next argument is value if true this is nothing if the applied logical test is TRUE i.e. if “Date 1” is equal to “Date 2” what is the value needed?. So, we need value as ““Both are Correct”.
- The next argument is value if false so if the logical test is not correct then what is the value needed, we need the values as “Both are Not Correct”.
Ok, we are done with the formula, close the bracket and hit enter key to get the result.
Drag the formula to other cells.
So, results are here instead of default TRUE or FALSE.
#2 – Compare If Date is Greater or Smaller
We have seen one set of calculation, now we will see whether one date is greater than or lower than another date. For this look at the below example data.
First, we will check “Date 1” is greater than “Date 2” or not as follows.
There you go we have results.
Similarly, we just need to change the logical operator in excel from greater than symbol to less than symbol to do the other way around the calculation.
Like this, we can compare dates in excel. However, there are certain things you need to keep in mind about this.
Things to Remember
- The date is stored as serial numbers so you need to be aware of it.
- There are situation date is stored as a text value in such cases date looking values are not dates so you need to convert to date.
This has been a guide to Compare Dates in Excel. Here we discuss how to compare dates in excel to know if the date is equal to, greater than or smaller than other dates. You can learn more from the following articles –