Excel Date Comparison (Greater/Less Than, Mismatches)
Working with dates is one area that frequently causes problems for Microsoft Excel users.
This is mostly due to Excel’s flexible date formatting options.
Therefore, even though you may mistakenly believe that two dates you see in Excel are the same when they contain different values (or vice versa, you may think two cells have different dates, and they may be the same).
I’ll go through a few methods in this lesson that you can use to compare dates in Xcel.
When you need to determine whether the dates in two cells are the same or not, or whether one date is larger than or less than the other date, this might be helpful.
Then let’s get going!
This instruction explains:
1. How do dates become stored in cells in Excel? 2. Date comparison in Excel 2.1 Verify whether the dates match or not. 2.2 Date Comparison Using the Greater-Less-Or-Less-Than (IF) Formula 3. Comparison of Dates with Time Values 4. Operators Excel Users Can Use When Comparing Dates
MS EXCEL VBA
How do dates become stored in cells in Excel?
Let me first describe how date and time values are stored in Excel before I discuss how to compare two dates in Excel.
Don’t skip this section if you don’t already know this since it’s crucial (I will keep it short).
In Excel’s backend, time data are kept as decimal values while dates are stored as whole integers.
Excel bases its dates on the first day of the year 1900, therefore a cell with the value 1 formatted as a date would display the date as January 1st, 1900.
Similarly, 44562 would stand for January 1, 2022. (which means that 44562 days have passed between 01 Jan 1900 and 01 Jan 2022).
Time is also represented by decimal numbers, with 0.5 denoting 12 hours and 0.75 denoting 18 hours.
Therefore, the value 44562.5 in a cell would represent January 1, 2022, at noon.
In a nutshell, backend numbers that are structured to display dates and times are used to store date and time data. This enables users to do computations using these dates and timings.
This is another justification for why not all date formats are supported by Excel. For instance, if you typed January 1st, 2022 into an Excel cell, Excel would treat this as a text string rather than a valid date format.
Date comparison in Excel:
Let’s examine how to compare two dates in Excel now that we have a better grasp of how dates and time data are handled in the Excel backend.
Verify whether the dates match or not.
Below is a dataset that has two sets of dates in two columns. I want to determine whether or not these dates are the same.
A straightforward equal-to operator may be used to do this.
=A2=B2
If the dates being compared are identical, the formula above will return TRUE; otherwise, it will return FALSE.
When we apply this formula, Excel merely checks to see whether the date numeric value is the same because dates are stored as numeric values.
In Excel, comparing dates is the same as comparing two integers.
You might also enjoy the following Excel tutorials:
When comparing dates, you should keep the following in mind:
- Dates can have two different formats and still be equal because their underlying numeric values are the same.
- Even dates that appear to be the same might differ. Look at rows #5 and #7 in the sample from above. Although the date appears to be the same, the outcome is FALSE. This occurs when a date is structured to merely display the date even if it also includes a time component. The dates in column B in this instance also include some time, how, ever it is hidden in the cell.
- You won’t be able to compare these dates if you entered them as strings (your dates need to be in an acceptable date format)
Date Comparison Using the Greater-Less-Or-Less-Than (IF) Formula:
While a direct comparison using the equal-to operator is perfectly acceptable, using an IF formula might make your comparison more insightful.
Below are two columns of dates, and I’m trying to determine if the dates in column B happened before or after the dates in column A.
This will allow me to determine if the report was sent in on time or after the deadline.
The equation to accomplish this is given below:
=IF(C2<=B2,"In Time","Delayed")
If the submission date is earlier than the due date, the above formula displays “In Time,” or else it displays “Delayed.” It compares the two dates using the less than or equal to operator.
You might also enjoy the following Excel tutorials:
The IF formula allows for additional possibilities (such as nesting multiple IF statements in the same formula).
The formula below will display the text “Delayed” if the report is submitted more than five days after the due date, “Grace” if it is submitted within five days of the due date, and “In-Time” if it is submitted earlier than the due date (or the last day of submission)
=IF(C2-B2<=0,"In Time",IF(C2-B2<=5,"Grace","Delayed"))
We need to check for three criteria, hence the above method employs two IF formulae (also known as nested IF formulas).
Because dates are saved in the backend as whole numbers, you’ll see that I’ve deducted the dates from one another. The total number of days between these two dates is therefore determined by subtracting one date from the other.
Comparison of Dates with Time Values:
As I have said, Excel stores time as a decimal integer and dates as whole numbers.
People frequently style their cells such that only the date is shown and the time component is hidden.
In the example below, both columns have the same values, but column B’s dates are formatted to only display the date portion, hiding the time component.
If you compare dates in Excel, this may make things unclear. For instance, in the dataset below, when I compare the dates in the two columns using the equal-to operator, I get surprising results.
Even though it seems like the dates are the same in the example above, cells C5 and C7 have a mismatch.
In this case, you may use the INT formula to ensure that the day portion of the date is being compared and the time portion is being disregarded.
The equation that will provide the desired outcome is listed below:
=INT(A2)=INT(B2)
When comparing the dates, the INT component of the calculation ensures that only the completed days are taken into account and the decimal part is discarded.
Even though this is a rare occurrence, it is wise to carefully check the cell format while working with dates. I frequently discover that cells configured to simply show the date portion are included in downloads from various databases that contain date and time information.
Operators Excel Users Can Use When Comparing Dates:
Finally, the following operations can be applied when comparing dates in Excel:
- Equivalent (=)
- More Than (>)
- Lower Than ()
- More than (>) or equivalent to
- Equal to or less than (=)
- Not the same as (>)
In this article, I demonstrated how to compare dates in Excel by utilizing the IF function and basic operators. I also spoke with you how to compare dates when the time value is included.
I sincerely hope this instruction was helpful.
You might also enjoy the following Excel tutorials:
