One area that often troubles MS Excel users is working with dates.
This is mainly because dates can be formatted in many different ways in Excel.
So while you may see two dates in Excel and believe those are the same, there is a possibility that these might be different values in the back end (or vice versa, you may think two cells have different dates, and it may be exact).
In this tutorial, I Ms Excel VBA offer you a couple of techniques that you can use to compare dates in Èxcel.
Before I get into how you can compare two dates in Excel, let me explain how date and time values are stored in MS Excel.
It is essential, so if you don’t know this already, don’t skip this section (I will keep it short).
Dates are stored as whole numbers in the back-end in Microsoft Excel, and time values are stored as decimal values.
The dates in Excel start from 01 Jan 1901, which means that the value 1, when formatted as a date, would show you 01-01-1901 as the date in the cell in Ms. Excel.
Similarly, 44562 would represent 01 Jan 2022 (44562 days have passed between 01 Jan 1900 and 01 Jan 2022).
Likewise, time is stored as decimal values, where 0.5 would represent 12 hours and 0.75 would represent 18 hours.
So if I have the value 44562.5 in a cell, this would represent 01 Jan 2022 at midnight.
This is also why not all date formats are acceptable formats in Excel. For example, if you enter Jan 01, 2023, in a cell in Excel, this would be treated as a text string and not as a valid dates format.
Comparing Dates in Excel
Now that we better understand dates and time values are handled in the Excel backend let’s see how to compare two dates in Excel.
Check Whether the Dates are the Same or Not
Below I have a datasets where I have two sets of dates in two columns, and I enjoy checking whether these dates are the same or not.
It can be done using a simple equal-to-operator.
=A2=B2
The above formula would return TRUE if the compared dates are the same and FALSE if they are not.
Since dates are stored as numeric values, when we use this formula, MS Excel checks whether the date numeric value is the same or not.
Comparing dates in MS Excel is just like comparing two numbers.
Rare things important thing you must know when comparing dates:
- Dates can be in two distinct formats and yet be equal, as the back-end numeric values of these dates are the same.
- Dates that look the same can be different. In the above example, look at rows #5 and #7. The date looks the same, but the result says FALSE. This happens when the date has a time part, but it’s formatted only to show the date. In this case, the dates in column B also have some time added to it (but it doesn’t show in the cell).
- In case you have a date entered as a string, you won’t be able to approximate these (your dates need to be in an acceptable date format)
Compare Dates Using IF Formula (Greater Less/Less Than)
While a head-on comparison with an equal-to operator works fine, your comparison could be more meaningful when using an IF formula.
Below, I have dates in two different columns, and I would like to know whether the dates in column B occurred before or after column A.
This choice helped me identify whether the reports were submitted before or after the specified due date.
Below is the formula that You will do this:
=IF(C2<=B2,"In Time","Delayed")
The overhead formula approximates the two dates using the less than or equal to operator, and if the submission date is before the due date, it shows ‘In Time’; else, it shows delayed.
You can do more with the IF formula (nesting multiple IF statements in the same formula).
Below is the formula that will show the text Delayed’ if the report is submitted 5 days after the due date, and it will show ‘Graces’ if the report is submitted within 5 days of the due date, and return ‘In-time’ if submitted before the due date (or the last day of submission)
=IF(C2-B2<=0,"In Time",IF(C2-B2<=5,"Grace","Delayed"))
The above formula uses two IF formulas (called nested IF formulas), as we need to review for 3 conditions.
I have subtracted the dates from each other, which is possible as dates are stored as whole numbers in the back-end. So when I subtract one date from another, it gives me the total digit of days between these two dates.
Compare Dates That Have Time Values
As I mentioned earlier, dates are stored as whole numerals, and time is stored as a decimal number in Excel.
People often format their cells only to show the date and hide the time.
Below is a sample where I have the same values in both columns, but the dates in column B are formatted only to show the date part, so you don’t see the time part in it.
This can lead to confusion when comparing dates in MS Excel. For example, I have a datasets below, and when I use the equal-to operator to compare the dates in the two columns, I get unexpected results.
In the above, I reach a mismatch in cells C5 and C7, while it appears that the dates are the same.
In such a strategy, you can use the INT formula to make sure you’re comparing only the day part of the date and the time part is ignored.
Below is the formula that will deliver us the correct result:
=INT(A2)=INT(B2)
The INT part of the formula makes sure that only the completed days are considered while comparing the dates, and the decimal part is forgotten.
While this is an uncommon scenario, it is always good to check the cell format when working with dates. I have often found that downloads from several databases containing date and time values have cells formatted only to show the date part.
Operators You Can Use When Comparing Dates in Excel
And finally, below are some operators you can use when comparing dates in Excel:
- Equal to (=)
- Greater Than (>)
- Less Than (<)
- Greater Than or Equal to (>=)
- Less Than or Equal to (<=)
- Not Equal to (<>)
This tutorial covered how to compare dates in Excel using simple operators and the IF function. I also covered how to handle comparing dates when you have the time value as a part of it.
I hope you found this helpful tutorial!
Other MS Excel tutorials you may also like:
