How to Compare Dates in Excel (2 Methods)

Dataset Overview

We’ll use the following dataset to demonstrate these methods. We have two columns: Predicted Delivery Date and Delivery Date. Our goal is to determine if the predicted delivery date was correct.

Method 1 – Using Logical Operators

Create a new column called Matching Status. In the first cell of this column, use the formula:

=D5=E5

Drag the Fill Handle down to populate the result in the rest of the cells.

Other Logical Operators:

You can also use other logical operators to compare dates:

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

Method 2 – Using the IF Function

Create a new column called Delivery Status.
Use the following formula:

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

- If the delivery date is on or before the predicted date, it will show In Time.
- Otherwise, it will show Delayed.

You can modify the formula to compare two dates directly:

=IF(D5=E5,"Matched","Unmatched")

Comparing Dates with Today’s Date (2 Methods)

Method 1 – Using the TODAY Function

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

Click on the image for a detailed view

Replace the Delivery Date with the current date (use TODAY()) in cell E5.

Apply the logical equal operator as before:

=D5=E5

Method 2 – Combining IF and TODAY Functions

Compare any date with the current date using this modified formula:

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

Calculating Duration Between Dates

Method 1 – Calculating Day Differences

Below is the overview of the DATEDIF function that we will be using.

Click on the image for a detailed view

Use the DATEDIF function to find the day difference between two dates:

=DATEDIF(D5,E5,"d")

Method 2 – Calculating Month Differences

To find the month difference, use the formula below:

=DATEDIF(D5,E5,"m")

Read More: VBA Compare Dates to Today

Method 3 – Calculating Year Differences

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

=DATEDIF(D5,E5,"y")

Method 4 – Finding the Exact Duration Between Two Dates

This method provides output that includes the difference in years, months, and days. Here’s the breakdown of the formula:

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

Formula Breakdown

The DATEDIF function extracts the year and month duration between dates in cells D5 and E5.
The ampersand operator (&) joins the text we write after each DATEDIF function.
Lastly, we subtract the month and year from the E5 cell data using the DATE function (which includes the YEAR and MONTH functions).
- DATEDIF(D5, E5, “y”) & ” years,” returns 2 years in the output cell (F5), representing the number of years between the two dates.
- DATEDIF(D5, E5, “ym”) & ” months,” returns 0 months, indicating the number of months passed between the dates.
- Since the DATEDIF function has limitations in calculating exact days, we subtract the month and year from the E5 cell data in the portion E5 – DATE(YEAR(E5), MONTH(E5), 1) & ” days”. This results in 24 days in cell F5.

Things to Remember

Format the cell as a Date Format when entering dates.
In our example, we used the USA date format (mm/dd/yy).
For comparing dates, we used logical operators (= and >) inside the IF function. You can use different operators as needed.

Download Practice Workbook

You can download the practice workbook from here:

Comparing Dates.xlsx

Frequently Asked Questions

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

Suppose your dates are in column B, and the specific date you want to compare against is in cell C5.
You can use the COUNTIF function with the following formula:
```
=COUNTIF(B:B, "<" & C5
```
This counts the number of dates in column B that are earlier than the date in cell C5. You can adjust the logical operator (<) as needed.

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

To find the earliest date in any range (e.g., B1:B10), use the MIN function:
```
=MIN(B1:B10)
```
For the latest date, use the MAX function:
```
=MAX(B1:B10)
```

3. Can I compare dates as text?

While you can compare dates as text using string functions (like TEXT), it’s recommended to work with actual date values. This ensures accurate comparisons and calculations.

Excel Compare Dates: Knowledge Hub

<< Go Back to Compare | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!