This guide will explain how to find it if a certain date comes before another date in Excel.
Since valid dates in Excel are considered a type of numerical data, we can use comparison operators to determine which dates come first.
Dates in Excel are stored as serial numbers to be used for calculations. By default, January 1st, 1900, is stored with the value of 1.
The date September 7th, 2022 is stored as the value 44,811. This number is equivalent to the number of days between the given date and January 1st, 1900.
Since Excel stores dates as numbers, we can use comparison operators to determine which of the two dates comes earlier.
For example, let’s say we have a table of deliverables. Each deliverable has a corresponding value under the Due Date column.
We want to know which deliverables are overdue as of the current date. How can we do this in Microsoft Excel?
We can use comparison operators to determine whether a specific date comes before the current date. The user can set up a new column labeled ‘Overdue’ that holds either a TRUE or FALSE value depending on the comparison’s result.
Now that we have a grasp on when to compare dates in Excel, let’s see how it looks on an actual Excel spreadsheet.
A Real Example of Comparing Dates in Excel
The following section provides several examples of how to compare dates in Excel. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a real example of the function being used in an Excel spreadsheet.
In the table below, we have three tasks with due dates. We also have the current date in cell C2. In column D, we added a formula that checks whether each task’s due date is earlier than the current date. If this is the case, then we mark that task as overdue.
To get the value in cell D5, we just need to use the following formula:
=D5 < $C$2
We can also use the
DATEVALUE function to convert a string to a valid date. For example, if we want to determine whether a date comes before January 1st, 2023, we can use the following formula:
=D5 < DATEVALUE(“2023-01-01”)
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try comparing dates in Excel, head over to the next section to read our guide on how to do it!
How to Compare If Date Is Before Another Date in Excel
This section will guide you through each step needed to start comparing dates in Excel. You’ll learn how we can use comparison operators to determine if a given date comes before another date.
In this example, we’ll use a tracker that contains a list of tasks and their due dates. We want to compare the task’s due date with the current date to check if the task is already overdue.
Follow these steps to start comparing dates in Excel:
- In the example below, we used the less than operator ‘<’ to compare the task’s due date with the current date. The current date can be generated using the
TODAYfunction. We set the current date to an absolute reference $C$2, so we can copy the formula using the Fill Handle tool.
- We may also use the
DATEVALUEfunction to return a date we can compare with. In the example below, we want to compare the task’s due date with the date ‘2022-09-01’. We must use
DATEVALUEto convert this string into a valid date value.
- In the past two examples, we’ve returned either TRUE or FALSE depending on the result of our comparison. We can use the IF function to return custom strings instead of the actual Boolean value.
For example, we can return either ‘Yes’ or ‘No’ in the Overdue column.
- Lastly, you may encounter issues when handling dates with times in them. When you make your comparison, use the
INTfunction to ignore the time.
This step-by-step guide is a quick introduction to comparing whether a date is before another date in Microsoft Excel. We’ve shown how to compare dates in Excel using comparison operators.
You should also know how to use the
IF function to return a custom string based on the output of the comparisons.
This function is just one example of the many Excel functions that you can use for your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
For example, you can read this guide to learn more about other use cases for comparison operators, such as the less than or equal to operator.
With so many other Excel functions available, you can find one appropriate for your use case.
Subscribe to our newsletter, and you’ll get exclusive access to the latest spreadsheet guides and tutorials from us.