Excel: How to Compare Dates Without Time


Often you may want to compare two dates in Excel while ignoring the time values associated with the dates.

Fortunately you can use the INT() function in Excel to extract just the date from a datetime value, which allows you to easily compare dates while ignoring the time.

The following examples show how to use this INT() function in different scenarios.

Example 1: Compare Dates Without Time

Suppose we have the following list of datetime values in Excel that show when different people signed up for a 5K race along with the cutoff date for signing up:

We can type the following formula into cell B2 to compare the sign up date with the cutoff date:

=IF(INT(A2)<=$D$2, "Valid", "Not Valid")

We can then drag and fill this formula down to every remaining cell in column B:

This formula compares only the date in column A with the cutoff date in cell D2.

If the date in column A is equal to or prior to the date in cell D2, the formula returns Valid.

Otherwise, the formula returns Not Valid.

Example 2: Compare and Count Dates Without Time

Once again suppose we have the following list of datetime values in Excel that show when different people signed up for a 5K race along with the cutoff date for signing up:

We can type the following formula into cell F2 to count the total number of sign up dates that are equal to or prior to the cutoff date:

=SUMPRODUCT((INT(A2:A10)<=D2)+0)

From the output we can see that 5 total dates in column A are equal to or prior to the cutoff date.

By using the INT() function, we were able to compare only the date portion of the datetime values in column A to the cutoff date.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Use SUMIFS with a Date Range in Excel
How to Filter Dates by Month in Excel
How to AutoFill Dates in Excel

Leave a Reply

Your email address will not be published.