Everyone works with dates in Excel. Often a spreadsheet will contain multiple date columns. And a common question is “how to compare dates in Excel?”.
In this tutorial, we explain how dates are stored in Excel and see many examples of comparing dates including the use of numerous functions from our Excel functions list.
Download the sample file to follow along with the practical examples.
How does Excel Store Dates in the Cells?
Excel stores dates as whole numbers known as serial numbers. Each day, one is added to the serial number of the previous date.
For example, the Excel date, or serial number, for the 1st May 2023 is 45047.
Of course, these number mean little to us, so a date format would be applied to represent it clearly.
As Excel stores dates as whole numbers, performing common tasks such as calculating date difference and comparing two dates are relatively simple.
What is the Date Format in Excel?
It is important when you enter dates, that you use a format that suits the region specified in your Excel settings.
I am in the UK, so the date is entered in the structure of day-month-year, and can be entered in any of the following formats (not all possible formats are shown).
01/05/2023
01-05-2023
1 May 2023
For my region, entering a date in the format 01.05.2023 would not be recognised, as the dot is used as a decimal separator. As I say, the date structure and format is specific to your region.
The date in Excel is stored as a serial number, though often presented immediately in the default date format. You can, of course, format the date however you like.
However, let’s look at a few methods to compare dates in Excel.
How do I Compare Two Dates in Excel?
Let’s now look at how to compare dates in Excel. We will see how to test if a date is before another date, use the IF function to display a value, and use Conditional Formatting to colour code due dates.
How do you Check if a Date is Before Another Date in Excel?
It is common for users to ask how to check if a date is before another date in Excel. This is often to test if the date value in a cell is before todays date, i.e., the date has passed, or expired.
If the date to test is in cell B2, then the formula can perform a simple logical test to check if the date is before todays date.
=B2<TODAY()
This Excel formula uses the TODAY function to return the current date. This ensures that the formula will continue to work each and every day.
Using the IF Function to Compare Dates
Performing a logical test to check if a date is before another date is useful, but returning the value of TRUE or FALSE alone is probably not enough. You may want to return a specific value based on the result of the logical test.
For this, the IF function is used to compare two dates in Excel and return “Yes” if the date is before todays date, and otherwise return an empty string making the cell appear blank. Of course, any value or action can be used instead of “Yes” and “”.
=IF(B2<TODAY(),"Yes","")
Of course, examples are not limited to testing against todays date, it may be that you want to test if a date in one date column is before the date in another column.
For example, this formula tests if the expected delivery date in column B is before the date delivered in column C, and returns the appropriate value. The WORKDAY.INTL function in Excel was used to calculate the expected delivery date.
=IF(B2<C2,"Yes","")
How do I Colour Code Due Dates in Excel
Using Conditional Formatting, you can colour code a due date that has passed. This is a useful alternative to returning a value when comparing dates.
Select the dates to compare.
Click Home > Conditional Formatting > Highlight Cells Rules > Less Than
Enter the TODAY function into the Format cells that are less than box
=TODAY()- Choose the cell format you want to apply from the list and click OK.
The date values that are less than todays date are highlighted by the Conditional Formatting rule.
The current date is the 1st June 2023, so two dates have been formatted.
See more examples of using Conditional Formatting with dates in Excel.
How to Calculate Date Difference in Excel
Finding the date difference in days, month, or years is a common request for Excel users. Fortunately, there are a few methods for this.
How to Subtract Dates in Excel
To return the number of days between two dates in Excel, you subtract one date from the other.
The following formula returns the days difference between the dates in cells A2 and B2. Cell B2 contains the later date so is used first in the Excel formula.
=B2-A2
Microsoft Excel even has a function for this named DAYS, but this is not required. The DAYS formula equivalent would be as follows.
=DAYS(B2,A2)How to Calculate Working Days Difference
Return the working days difference between two dates in Excel with the NETWORKDAYS.INTL function. This date function very useful.
The function requires the two dates Excel is to compare, and the optional parameters of the weekend days of your working week and any date values to be considered as non-working days.
This Excel formula returns the number of working days between the dates in columns A and B. The weekend is specified as Sunday and Monday only by using index 2, and no other date is given for non-working days.
=NETWORKDAYS.INTL(A2,B2,2)
Using the DATEDIF Function
DATEDIF is a date function that enables you to compare dates in Excel and return the number of days, months, or years between the two dates. It requires a start date, end date and the units to use when comparing dates.
For example, the following formula returns the number of years between two dates.
=DATEDIF(A2,TODAY(),"y")It uses the date values from column A, the TODAY function to return the current date, and specifies complete years as the interval by using the string “y”.
This formula can be used to calculate age or length of service in Excel.
And this formula returns the date difference in years and months. It uses two DATEDIF functions and merges the results into one string.
=DATEDIF(A2,TODAY(),"y")&" Years, "&
DATEDIF(A2,TODAY(),"ym")&" months"
Watch more on how to use the DATEDIF function to calculate length of service.
Wrap Up
In this tutorial, we covered many methods of how to compare dates in Excel. These included highlighting due dates, using the IF function, and calculating the difference (and working days difference) between the dates Excel compares.
To learn over 150 Excel functions (including an entire 100-page chapter on Excel date functions), pick up a copy of Advanced Excel Formulas. More than 500 formula examples with practise files to follow along.
Leave a Reply