How to Compare Dates in Two Columns in Excel (8 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Need to compare dates in two columns? Microsoft Excel has some formulas to compare two dates. If you want to learn those formulas, this article is for you. Here we have discussed how to compare dates in two columns in Excel.


How to Compare Dates in Two Columns in Excel: 8 Easy Methods

In Excel, there is an exciting formula utilizing IF, COUNTIF, DATE, and TODAY functions to compare dates in two columns. Also, there is a feature Conditional Formatting in Excel where you can also compare dates in two columns.

1. Compare Dates in Two Columns Whether They Are Equal or Not

In your dataset, you may have a huge set of data including dates that are the same. Now you want to sort out whether they are the same or not. Follow the simple steps to do that.

📌 Steps:

  • First, select the cell D5 and write =B5=C5. It means whether the value is the same or not in those cells.

Compare Dates in Two Columns Whether They Are Equal or Not

  • Next, drag down the Fill Handle for other cells.

Compare Dates in Two Columns Whether They Are Equal or Not

  • Hence, the result is displayed in binary TRUE or FALSE.


2. Compare Dates in Two Columns Whether They Are Equal or Not with the IF Function

The set of dates can be compared using the IF Function.

📌 Steps:

  • First, click on cell D5 and write down the formula stated below.
=IF(B5=C5,”Match”,”Not Match”)
  • Press ENTER.

Compare Dates in Two Columns Whether They Are Equal or Not with the IF Function

  • Drag down the Fill Handle for other cells and your result will be shown in the match and not match.

Compare Dates in Two Columns Whether They Are Equal or Not with the IF Function


3. Compare If Dates Are Greater or Smaller

In Excel, we can compare dates in two columns which is greater and which is smaller.

📌 Steps:

  • Select cell D5 and write down =B5>C5

Compare If Dates Are Greater or Smaller

  • Press ENTER and drag down Fill Handle. It will show you which value is greater in two columns.

Compare If Dates Are Greater or Smaller

  • Then select cell E5 and write down B5<C5.

  • Press ENTER and drag down Fill Handle.
  • Thus this will show you the binary result of TRUE or FALSE that the date of column B is smaller than column.

Compare If Dates Are Greater or Smaller


4. Compare Dates with IF and DATE Functions

You can use the IF and DATE functions easily to compare dates in two columns.

📌 Steps:

  • First, select the cell E5 under the remark section and write down the formula
=IF(DATE(2022,9,15)>=C5,”On Time”,”Delayed”)

Here,

DATE(2022,9,15) denotes the date of the deadline. Besides,

C5 denotes the date of submission.

Formula Breakdown:

  • DATE(2022,9,15)→take input 15-09-22.
  • IF(15-09-22>=C5, “On Time”, “Delayed”) compares if the date 15-09-22 is greater than or equal to the date of cell C5. It finds the logic true and so, returns “On Time”. Otherwise, it would return “Delayed”.

Compare Dates with IF and DATE Functions

  • Then drag down the formula for other cells.


5. Using IF Function with AND Logic to Compare Two Dates

We can use the IF with AND function to compare dates with start and end date deadlines.

📌 Steps:

  • Select cell F5 under the remark section and write the formula
=IF(AND(C5>=$E$7,C5<=$E$8),”On Time”,”Delayed”)

In the above formula, C5, E7, and E8 refer to the date of Submission, the Start date of submission and the End date of submission respectively.

  • Press ENTER

Formula Breakdown:

AND(C5>=$E$7,C5<=$E$8)→checks if C5 is in between cell E6 and E7

=IF(AND(C5>=$E$7,C5<=$E$8),”On Time”,”Delayed”)→if the value is in E7 and E8 it will return “On Time” otherwise it will return “Delayed”.

Using IF Function with AND Logic to Compare Two Dates

  • Drag down this formula for other cells.


6. Applying Excel IF and TODAY Functions to Compare Two Dates

Using the TODAY Function is the easiest way to get the current date as well as time.

📌 Steps:

  • Select cell D5 and write the below formula.
=IF(TODAY()>C5,”On Time”,”Delayed”)

Formula Breakdown:

TODAY()>C5→ Compares the present day with cell C5.

=IF(TODAY()>C5,”On Time”,”Delayed”)→If the logic is true it returns “On Time” otherwise it returns “Delayed”

Applying Excel IF and TODAY Functions to Compare Two Dates

  • Then drag down for other cells.

Applying Excel IF and TODAY Functions to Compare Two Dates


7. Using IF and COUNTIF Functions for Comparison Between Two Dates

📌 Steps:

  • First, in cell D5 write the formula
=IF(COUNTIF($B:$B, $C5)=0, “Not match”, “Match”)

Formula Breakdown:

COUNTIF($B:$B, $C5)=0→ Column B is compared with cell C5.

IF(COUNTIF($B:$B, $C5)=0, “Not match”, “Match”)→ If the logic is true it will return “Match” otherwise it will return “Not match”.

Using IF and COUNTIF Function for Comparison Between Two Dates

  • Lastly, drag down for other cells.


8. Applying Conditional Formatting to Compare Two Dates

We can use Excel’s built-in feature Conditional Formatting feature to compare two dates by highlighting the cells with color.

📌 Steps:

  • First Select the data under the C column >> Go to Home tab >> Select Conditional Formatting >> Click New Rule.

Applying Conditional Formatting to Compare Two Dates

  • A dialog box will pop out. Then select Use a formula to determine which cells to format >> Select cell C5 in the format box >> Click Format.

Applying Conditional Formatting to Compare Two Dates

  • Select Fill to select a color.
  • Press OK

Applying Conditional Formatting to Compare Two Dates

  • Then the color will be in the preview box and click Format.

  • Finally, your data will be formatted with colors that are different from the deadlines.

Applying Conditional Formatting to Compare Two Dates


Practice Section

We have provided a Practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

So, these are some easy formulas to Compare Dates in two Columns. Please let us know in the comment section if you have questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.


<< Go Back to Dates | Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo