How to Compare If Date Is Before Another Date in Excel

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.

compare dates in Excel

 

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:

  1. 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 TODAY function. We set the current date to an absolute reference $C$2, so we can copy the formula using the Fill Handle tool.
    compare due date with current date
  2. We may also use the DATEVALUE function 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 DATEVALUE to convert this string into a valid date value.
    compare dates in Excel with DATEVALUE
  3. 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.
    use IF function to set custom output string
    For example, we can return either ‘Yes’ or ‘No’ in the Overdue column.
  4. Lastly, you may encounter issues when handling dates with times in them. When you make your comparison, use the INT function to ignore the time.
    compare dates in Excel with INT

 

 

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.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like