Fahim Shahriyar Dipto
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.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
8 Methods to Compare Dates in Two Columns in Excel
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.
- Next, drag down Fill Handle for other cells.
- Hence, the result is displayed in binary TRUE or FALSE.
Read More: How to Compare If Date Is Before Another Date in Excel
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.
- Press ENTER.
- Drag down Fill Handle for other cells and your result will be shown in the match and not match.
Read More: Excel Formula If One Date is Greater Than Another Date
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
- Press ENTER and drag down Fill Handle. It will show you which value is greater in two columns.
- 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.
Read More: Excel Formula If Date Is Less Than Today (4 Examples)
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
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”.
- Then drag down the formula for other cells.
Read More: If Cell Contains Date Then Return Value in Excel (5 Examples)
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
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”.
- Drag down this formula for other cells.
Read More: How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)
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.
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”
- Then drag down for other cells.
Read More: How to Compare Dates to Today with Excel VBA (3 Easy Ways)
7. Using IF and COUNTIF Functions for Comparison Between Two Dates
📌 Steps:
- First, in cell D5 write the formula
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”.
- Lastly, drag down for other cells.
Read More: Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)
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.
- 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.
- Select Fill to select a color.
- Press OK
- Then the color will be in the preview box and click Format.
- Finally, your date will be formatted with colors which are different from the deadlines.
Read More: Conditional Formatting for Dates Older Than Certain Date in Excel
Practice Section
We have provided a Practice section on each sheet on the right side for your practice. Please do it by yourself.
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 your better understanding please download the practice sheet. Visit our website Exceldemy to find out different kinds of excel methods. Thanks for your patience in reading this article.
