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, enter 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.
- Insert 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=E5Read More: Excel Conditional Formatting for Date Less Than 6 Months from Today
Method 2 – Combining IF and TODAY Functions
Compare any date with the current date using this modified formula:
=IF(D5>=TODAY(),"In Time","Delayed")
Read More: Conditional Formatting for Dates Older Than a Certain Date
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")
Read More: Conditional Formatting Based on Date Older Than 1 Year
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")
Read More: Check If Date is Within 7 Days of Another Date
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.
- 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.
Read More: Formula If Date Is Greater Than Another Date
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:
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, "<" & C5This 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!
