Compare two dates in Excel to unlock a myriad of possibilities for time-based data analysis, tracking, and management. Whether you’re calculating the tenure of employees, determining the time between events, or comparing financial periods, understanding how to effectively compare dates is crucial. This guide will walk you through the various methods to compare two dates in Microsoft Excel, providing you with the tools to perform accurate calculations, set up conditional formatting, or even trigger alerts based on date comparisons. By mastering this functionality, you’ll enhance your ability to manage deadlines, analyze time-dependent data, and present temporal insights in a clear, actionable manner.
This Topic Covers:
- How to compare two dates if dates are equal or not?
- Using (=) in a Formula to compare two dates
- Using IF function
- How to compare if a date is greater or smaller?
- Using (</>) in a formula
- Using IF function
- How to compare dates that have time values?
- What are the symbols that we can use while comparing dates in Excel?
1. How to compare two dates in Excel if dates are equal or not with another date?
Below is a dataset that has two sets of dates in two columns. Using a few simple and easy formulas you can check if these two dates are the same or not.
1.1 Using (=) in a Formula to compare two dates in the cells
Step 1: Select cell C2 and insert the formula given below. Click Enter key.
=A2=B2
Step 2: Drag C2 down to C8 to apply the formula in that whole column of this table. What this formula does is, it creates a comparison between the two dates and checks if the dates are equal or not. If equal then the cell will return TRUE and if the dates aren’t equal it will return FALSE.
1.2 Using IF function
If you don’t want the result in TRUE or FALSE and want the cells to return a meaningful text then IF function is the solution.
Step 1: Enter the formula given below inside cell C2 and press Enter key.
=IF(A2=B2,”Equal”,”Not Equal”)
Step 2: Drag C2 down to C8. This formula just changes the TRUE and FALSE text into Equal and Not Equal by using the text strings attached inside the formula.
2. How to compare if a date is greater or smaller?
2.1 Using (</>) in a formula
Follow the steps to check if Date 1 is greater ( > ) than Date 2 or not.
Step 1: Type the formula in C2 and click Enter.
=A2>B2
Step 2: To insert the formula in the whole column just drag C2 down to C8. The cells will return TRUE if Date 1 is greater than Date 2, otherwise it will return FALSE.
Now to see if Date 1 is smaller ( < ) than Date 2 or not use the following step.
Step 3: Insert the following formula in D2>>click Enter>>drag the cell downwards. If Date 1 is smaller than Date 2, the cells will return TRUE otherwise FALSE.
=A2<B2
2.2 Using IF function
You can add your preferable text strings to both these formulas by wrapping them with IF function in Excel.
Step 1: Use this formula in column C to check if Date 1 (start date) is greater than or not (from Date 2: end date). The cells return Greater or Smaller according to the attached text strings.
=IF(A2>B2,”Greater”,”Smaller”)
Step 2: This formula also works the same way as the previous one.
=IF(A2<B2,”Greater”,”Smaller”)
3. How to compare dates that have date and time values in date format?
Sometimes we use a format in our cells that hide time values and only shows the dates. In such cases , while making a comparison between two dates we may find difficulties comparing the dates because hiding the time value confuses the excel formulas which leads to a FALSE result as you can see in the picture below. Cell C4 and C6 are returning FALSE though both Date 1 and Date 2 are the same dates. In this case, you can use the INT formula to ensure that the day part of the date is being compared and the time part is being ignored.
Step 1: Insert the following formula in cell C2 and hit Enter.
=INT(A2)=INT(B2)
Step 2: Drag C2 down to C8. See the results, C4 and C6 have returned TRUE instead of FALSE as the formula now ignored the time part.
4. What are the symbols that we can use when comparing dates in Excel?
There are some symbols you can use while comparing two dates in Excel.
- Equal to (=)
- Not Equal to (<>)
- Greater Than (>)
- Less Than (<)
- Greater Than or Equal to (>=)
- Less Than or Equal to (<=)
We can also subtract one date from another to get the number of days to see the difference in dates.
For ready-to-use Dashboard Templates:
