How to Compare Dates in Excel?

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will demonstrate how to compare dates in Excel.

You will learn how to
– Compare dates whether they are the same
– Compare dates based on condition
– Compare dates by calculating day, month, or year differences
– Compare dates by finding the exact duration between two dates

We have used Microsoft 365 to prepare this article. However, the methods are applicable to all earlier Excel versions from 2007.

The key feature of comparing dates is to see the time left or check the deadline, which is important for business purposes like product delivery, managing schedules, work pace tracking, etc.

Excel Compare Dates

What Are the Ways to Compare Dates in Excel?

To compare dates in Excel, we will apply two methods including using the logical operators and the IF function. We will use the following dataset for the demonstration. There are two columns containing dates – Predicted Delivery Date and Delivery Date. We want to see whether the Predicted Delivery Date was correct in a new column.

Dataset to Compare Dates in Excel

1. Using Logical Operator

To compare dates in Excel, we will use logical operators to check whether the two dates match or not. Use the following procedure, which uses the logical Equal (=) operator to determine that. This will give True or False at the output cell.

We will have a new column named Matching Status. For the first cell in the column, use the formula below. Drag the Fill Handle for the rest of the cells.


Using Logical Operator to Compare Dates

Other Logical Operators that can be used to compare dates replacing the Equal (=)

  • The greater than operator (>)
  • The less-than operator (<)
  • The not equal operator (<>)
  • The greater than or equal (>=) and the less than or equal operator (<=)

2. Using the IF Function

For this method, we will use the IF function to check if the dates are equal. This formula in the new Delivery Status column will show whether the delivery was done before the Predicted Delivery Date or it was delayed for delivery. If it was delivered before or on the Predicted Delivery Date, it will show In Time. Otherwise, it will show Delayed.

Below is the overview of the IF function that we will be using in this case.

overview of excel if function

Click on the image for a detailed view

=IF(D5>=E5,"In Time","Delayed")

Use of IF function to check delivery status in excel

We can modify the formula for matching two dates with the formula below.


using excel formula for comparing two dates

What Are the Ways to Compare Dates with Today’s Date in Excel?

For comparing any date with today’s date, there are 2 different ways as before. We can replace that Delivery Date with the current date and apply the methods that we used earlier in the first case. The IF function and the logical operators will be used in this case as well. These are applicable when it comes to checking any deadline or creating a schedule. The methods are described below.

1. Using TODAY Function

You can use the TODAY function to compare the date’s equality with today’s date. Below is an overview of the TODAY function.

overview of excel today function

Click on the image for a detailed view

We used cell E5.


using today function for inserting current date

Then apply the logical equal operator like before in a new column.


using logical equal operator to compare date with current date in Excel

Read More: Excel Conditional Formatting for Date Less Than 6 Months from Today

2. Combining IF and TODAY Functions

To compare dates, we can also apply the IF function to compare any date with the current date in Excel. Here we will slightly modify the formula to use the TODAY function. Instead of comparing with the D5 cell, we will compare directly with the current date. The modified formula is below for application.

=IF(D5>=TODAY(),"In Time","Delayed")

using combination of the IF and TODAY function to compare any date with the current date

Read More: Conditional Formatting for Dates Older Than a Certain Date

What are the Ways to Compare Dates By Calculating the Duration In-Between in Excel?

To find out the time difference between the two dates for comparing them, we will use 4 different methods that use the DATEDIF function. We can use this formula to find duration in days, months, and years. Also, we can use a modified version of this formula to find out the exact difference containing days, months, and years. These methods and formulas are below with proper examples.

1. Calculating Day Differences

To find out the day difference between the two dates, we will use a formula using the DATEDIF function. Below is the overview of the DATEDIF function that we will be using in this case.

overview of excel datedif function

Click on the image for a detailed view

The formula for this method is below.


 using the dateif function for finding out time duration

Read More: Conditional Formatting Based on Date Older Than 1 Year

2. Calculating Month Differences

To find out the month difference, use the following formula to get the month difference between the two dates.


Using DATEDIF to Calculate Month Difference

Read More: VBA Compare Dates to Today

3. Calculating Year Differences

To find out the year difference, use the formula below.


Using DATEDIF to Calculate Year Difference

Read More: Check If Date is Within 7 Days of Another Date

4. Finding the Exact Duration Between Two Dates

To compare dates by finding the exact duration between two dates, we will use this method will give us output that contains days, months, and years difference. The formula is the combination of the three formulas we used in the 3 methods earlier. The formula for this method is below.

=DATEDIF(D5,E5,"y")&" years,"&DATEDIF(D5,E5,"ym")&" months,"&E5-DATE(YEAR(E5),MONTH(E5),1)&" days"

excel formula for duration between two dates in Excel

💡Formula Breakdown

  • The DATEDIF function extracts year and month duration from the D5 and the E5 cell data.
  • The ampersand operator (&) joins the text we wrote after each DATEDIF function.
  • Lastly, we subtract month and year from the E5 cell data using the DATE function. There was the YEAR function and the MONTH function in it.
  • DATEDIF(D5,E5,”y”)&” years,” – this portion of the formula returns 2 years, in the output cell F5. This means it finds out the number of years passed between the two dates.
  • Similarly, DATEDIF(D5,E5,”ym”)&” months,” – this portion returns 0 months, in the output cell. This means it finds out the number of months passed between the two dates.
  • Since the DATEDIF function has a problem while finding out exact the number of days that passed, we subtracted the month and year from the E5 cell data in the portion E5-DATE(YEAR(E5),MONTH(E5),1)&” days”. This will return 24 days in the F5 cell.

Read More: Formula If Date Is Greater Than Another Date

Things to Remember

  • Remember to format the cell in Date Format while entering dates.
  • In our example, we have used the date format in the USA which is mm/dd/yy.
  • For comparing dates, we have used logical operators “=” and “>” inside the IF function. Of course, you can use different operators.

Download Practice Workbook


Throughout this tutorial, you have learned different Excel methods for comparing dates. Different methods and multiple applications have been shown here for better understanding. Here we used logical operators, the IF function, the TODAY function, their combination, differences between dates, etc. These will help to check deadlines, keep track of schedules, calculate remaining time or duration, etc.

Check out the Knowledge Hub section of this article for similar articles related to Compare Dates in Excel. If you’re still having trouble with these examples or methods, let us know in the comments. Our team is ready to answer all of your questions. For any Excel-related problems, you can visit our website, Exceldemy, for solutions.

Frequently Asked Questions

1. How can I count the number of dates that are earlier than a specific date? 

Answer:  You can use the COUNTIF function. If your dates are in column B and the specific date is in cell C5, you can use the formula “=COUNTIF(B:B,”<“&C5)” to count the number of dates in column B that are earlier than the date in cell C5. Also, by changing the logical operator, you can find out the number of days later than the specific date as well.

2. How do I compare dates to find the earliest and latest dates in a range?

Answer: To find out the earliest date in any range, use the MIN function: “=MIN(B1:B10)” if your dates are in the range B1:B10. For the latest date, use the MAX function: “=MAX(B1:B10)“.

3. Can I compare dates as text?

Answer: Yes, you can compare dates as text using string functions like the TEXT function. However, it’s better to work with dates as actual date values. That way we can have accurate comparisons and calculations.

Excel Compare Dates: Knowledge Hub

<< Go Back to Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF