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.
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.
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.
=D5=E5
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.
Click on the image for a detailed view
=IF(D5>=E5,"In Time","Delayed")
We can modify the formula for matching two dates with the formula below.
=IF(D5=E5,"Matched","Unmatched")
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.
Click on the image for a detailed view
We used cell E5.
=TODAY()
Then apply the logical equal operator like before in a new column.
=D5=E5
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")
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.
Click on the image for a detailed view
The formula for this method is below.
=DATEDIF(D5,E5,"d")
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.
=DATEDIF(D5,E5,"m")
Read More: VBA Compare Dates to Today
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
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"
💡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
Conclusion
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
Get FREE Advanced Excel Exercises with Solutions!
