Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel Formula If One Date is Greater Than Another Date

Microsoft Excel has opened a new era in the field automation of calculation. You can just apply features and create formulas and Excel will make hundreds of calculations in the blink of an eye! You can calculate the difference between dates and make a comparison via this software. Sometimes, you may need to create an Excel formula if one date is greater than another date. If this task bothers you, then this article will definitely make it easy for you. In this article, I will show you the formation of Excel formula if one date is greater than another date.


Download Practice Workbook

You can download the practice book from the link below.


5 Ways to Apply Excel Formula If One Date is Greater Than Another Date

Let’s say, 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

From the date of submission, we want to find out if the assignment is submitted on time or delayed. If the date of Deadline is greater than the date of Submission, then we want to return “On Time” in the Remark section, and if not, we want “Delayed

In this section, you will find 5 suitable ways to use the Excel formula if a date is greater than another date. I will demonstrate them one by one here. Let’s check them now!


1. Use IF Function When One Date Is Greater Than Another

We will create a formula with the IF function when the Deadline is greater than the Submission Date. In order to demonstrate the method, proceed with the following steps.

Steps:

  • First of all, type 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

  • Then, hit 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

  • Now, drag the Fill Handle tool to the cells below to Autofill the formula for the next cells.

Autofill IF Formula

  • Hence, you will get the output for every cell you want.

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

Read More: Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)


2. Formula Combining IF and DATE Functions

You can combinedly apply the IF and the DATE functions when one date is greater than another.

For our previous set of data, we will now use the IF and the DATE functions.

Here, we won’t compare the cell of Submission Date with the Deadline, rather will directly use the Deadline in the formula. For this purpose, follow the steps below.

Steps:

  • Firstly, apply 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”.
  • Then, drag the formula for the other cells to get the same types of output.

Read More: Excel Formula If Date Is Greater Than 2 Years (3 Examples)


Similar Readings


3. Applying IF Function with AND Logic

You can compare between dates with the use of AND logic in the formulation of the IF function.

The AND function returns TRUE when all the logic is true and returns FALSE when any of the logic is false.

The dataset we have stated in the previous sections, let’s change it a bit. The deadline date has been ranged from 25-08-22 to 02-09-22.

IF function with AND logic dataset

Here, we will apply the IF function formulated with AND logic. So, let’s start the procedure.

Steps:

  • Firstly, Apply the following formula to 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”.
  • Then, drag the Fill Handle tool for the other cells to get the result.

Drag the IF Formula with AND logic in Excel

Read More: How to Use COUNTIF for Date Greater Than 30 Days in Excel


4. Combination of Excel IF and TODAY Functions

The combination of the IF and TODAY functions allows the comparison between two dates.

The TODAY function returns the date of today.

Let’s say, for the previous type of dataset, the deadline date is today, and you want to find out 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

If you want to see the application of these two functions, chase the steps below.

Steps:

  • First of all, apply 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.
  • Then, 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

Read More: How to Compare Dates to Today with Excel VBA (3 Easy Ways)


5. Applying Conditional Formatting If One Date Is Greater

For the same dataset, you can also apply the Conditional Formatting. Let’s say, we want to format the date of submission which has been submitted on time.

In order to do so, follow the steps below.

Steps:

  • First of all, 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

  • Then, the New Formatting Rule dialogue box will show up.
  • Here, 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

  • Now, click Format.

New Rule to Conditional Formatting in Excel

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

Format Cells Dialogue Box

  • After that, click OK to close the New Formatting Rule box.

  • Finally, the cells which match with the rule will be formatted as the color you have assigned.

Conditionally Formatted Date Greater Than Another DateRead More: Conditional Formatting for Dates Older Than Certain Date in Excel


Conclusion

In this article, I have tried to show you some Excel formulas if a date is greater than another date. I hope this article has shed some light on your way to apply formulas when comparing dates in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo