How to Use Excel Formulas to Compare Two Dates

We have a dataset of some Students who have to submit their assignments, the Date of Submission & the Deadline, and the Remarks (if the submission is on time or delayed).

Dataset to Apply Excel Formula If One Date is Greater Than Another Date


Method 1 – Using the IF Function to Compare If a Date Is Greater Than Another

Steps:

  • Enter the following formula to the first cell (i.e., E5) in the Remark section.

=IF($D$5>=C5,"On Time","Delayed")

Here,

  • D5= the Date of Deadline
  • C5= the Date of Submission

  • Press ENTER, and the cell will show “On Time” as the Deadline is greater than the Date of Submission, which means the assignment is submitted on time.

Apply IF Formula when One Date is Greater Than Another Date

  • Drag the Fill Handle tool to the cells below to Autofill the formula for the next cells.

Autofill IF Formula

You will get the output for every cell you want.

Apply IF Formula when One Date is Greater Than Another Date in Excel


Method 2 – Using the IF-DATE Formula to Compare Two Dates

For this dataset, we will use the IF and the DATE functions.

Steps:

  • Enter the following formula in the first cell of the remark section:

=IF(DATE(2022,9,2)>=C5,"On Time","Delayed")

Here,

  • DATE(2022,9,2)= the Date of Deadline
  • C5= the Date of Submission

IF-DATE formula when one date is greater than another date in Excel

Formula Breakdown

  • DATE(2022,9,2) takes the date 02-09-22 as input.
  • IF(02-09-22>=C5,”On Time”,”Delayed”) compares whether the date 02-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”.
  • Drag the formula for the other cells to get the same types of output.


Method 3 – Applying the IF Function with AND Logic to Compare Two Dates

The deadline date is ranged from 25-08-22 to 02-09-22.

IF function with AND logic dataset

Steps:

  • Enter the following formula for the selected cell in the Remark section.

=IF(AND(C5>=$G$5,C5<=$G$6),"On Time","Delayed")

Here,

  • G5= The Start Date of Deadline
  • G6= The End Date of Deadline
  • C5= The Date of Submission

Apply IF formula with AND Logic as One DAte is Greater Than Another in Excel

 Formula Breakdown

  • C5>=$G$5,C5<=$G$6) takes two conditions together, whether the date of submission is greater than the start date of the deadline and less than the end date of the deadline.
  • IF(AND(C5>=$G$5,C5<=$G$6),”On Time”,”Delayed”) checks the logic and returns “On Time” if it finds the logic true. Otherwise, it returns “Delayed”.
  • Drag the Fill Handle tool for the other cells to get the result.

Drag the IF Formula with AND logic in Excel


Method 4 – Combining the IF and TODAY Functions

The deadline is today, and you want to determine if the assignment is submitted today or will be submitted later.

Apply Excel TODAY Function when One Date is Greater Than Another Date in Excel

Steps:

  • Enter the following formula in the first cell of the remark section.

=IF(TODAY()>C5,"On Time","Delayed")

Here,

  • TODAY()= the Date of Today
  • C5= the Date of Submission

Formula Breakdown

  • TODAY()) returns the date of today by default ( 29-08-22)
  • IF(29-08-22>C5,”On Time”,”Delayed”) checks the logic and returns “Delayed” as it finds 29-08-22 is less than the comparing date 29-08-22.
  • Drag the Fill Handle tool to copy the formula for the next cells.

Drag formula in Excel as One Date is Greater than Another Date


Method 5 – Applying a Conditional Formatting Formula to Highlight Which Date Is Greater

Steps:

  • Select the range of data > go to the Home tab> click Conditional Formatting> select New Rule.

Application of Conditional Formatting if One Date is Greater Than Another Date in Excel

  • The New Formatting Rule dialogue box will pop up.
  • Click Select a formula to determine which cells to format in the Select a Rule Type field and type the formula in the Format values where this formula is true field.

=$D$5>=C5

  • Click Format.

New Rule to Conditional Formatting in Excel

  • The Format Cells pop-up will appear. Go to Fill> select a color> click OK.

Format Cells Dialogue Box

  • Click OK to close the New Formatting Rule box.

  • The cells matching the rule will be formatted as the color you have assigned.

Conditionally Formatted Date Greater Than Another Date


Download the Practice Workbook

You can download the practice book from the link below.


<< Go Back to Dates | Compare | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo