Table of Contents
Comparing dates without time in Excel can be done by using the DATEVALUE function to convert a date to an integer value. This integer can then be used in a comparison formula, such as IF or VLOOKUP, to compare two dates. This method is useful for comparing dates in different formats or from different sources. The result of the comparison formula will be a Boolean value of TRUE or FALSE depending on the comparison result.
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.