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).
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.
- Drag the Fill Handle tool to the cells below to Autofill the formula for the next cells.
You will get the output for every cell you want.
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
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.
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
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.
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.
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.
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.
- 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.
- The Format Cells pop-up will appear. Go to Fill> select a color> click OK.
- Click OK to close the New Formatting Rule box.
- The cells matching the rule will be formatted as the color you have assigned.
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!