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

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

While working with Microsoft Excel, sometimes we need to calculate the date which is greater than 2 years for the convenience of our work. Calculating a date that is greater than 2 years in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn three quick and suitable ways to calculate the date if a date is greater than 2 years using the Excel formula effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Examples with Excel Formulas to Return Value If Date Is Greater Than 2 Years

Let’s assume we have an Excel large worksheet that contains the information about several sales representatives of XYZ Group. The name of the sales representatives, the Joining Date, and Resigning Date of the Sales representatives are given in Columns B, C, D, and E respectively. From our dataset, we will calculate the working durations of those employees using the DATEDIF, IF functions, and Conditional Formatting. We can easily apply the DATEDIF, IF functions, and Conditional Formatting in Excel to calculate the working duration using Excel formula if date is greater than 2 years with XYZ company. Here’s an overview of the dataset for today’s task.

excel formula if date is greater than 2 years


1. Use DATEDIF Function to Return a Specific Text If Date Is Greater Than 2 Years

In this section, we will apply the DATEDIF function to calculate the working duration of the employees of XYZ company. After that, we will use the IF function to identify those employees who are working with XYZ company for more than 2 years. From our dataset, we can easily do that. This is an easy and time-saving task. Let’s follow the instructions below to learn!

Step 1:

  • First of all, select cell E5 for the convenience of our work.
  • After selecting cell E5, type the following formula in the Formula Bar,
=IF(DATEDIF(C5,D5,"Y")>2,"YES","NO")

Formula Breakdown:

  • Inside the DATEDIF function, C5 is the start_date, D5 is the end_date, and “Y” is the return type.
  • If the output of the DATEDIF function is greater than 2, then the IF function returns YES otherwise it returns NO.

Using DATEDIF Function to Return a Specific Text If Date Is Greater Than 2 Years

  • Hence, press Enter on your keyboard. As a result, you will be able to get the return of the functions and the return is YES.

Step 2:

  • After that, AutoFill the functions to the rest of the cells in column E which has been given in the screenshot.

Using DATEDIF Function to Return a Specific Text If Date Is Greater Than 2 Years

  • Further, we will fill those cells with Yellow whose return is less than 2 years. To do that, we will use the Fill option from the Home ribbon.

Using DATEDIF Function to Return a Specific Text If Date Is Greater Than 2 Years

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


Similar Readings


2. Apply IF Function to Return a Specific Text If Date Is Greater Than 2 Years

Now, we will use the IF function to calculate the working duration of the XYZ employees. From our dataset, we will calculate those employees who are working more than 2 years or 730 days with XYZ company using the IF function. Let’s follow the instructions below to learn!

Step 1:

  • First of all, select cell E5 for the convenience of our work.
  • After selecting cell E5, type the following IF function in the Formula Bar,
=IF((D5-C5)>730, "YES", "NO")
  • Where D5 is the Resigning date and C5 is the Joining date.
  • If the output of the D5-C5 is greater than 730, then the IF function returns YES otherwise it returns NO.
  • Hence, simply press Enter on your keyboard. As a result, you will be able to get the return of the IF function and the return is NO.

Applying IF Function to Return a Specific Text If Date Is Greater Than 2 Years

Step 2:

  • After that, AutoFill the IF function to the rest of the cells in column E which has been given in the screenshot.

  • Further, we will fill those cells with green whose return is greater than 2 years or 730 days. To do that, we will use the Fill option from the Home ribbon.

Applying IF Function to Return a Specific Text If Date Is Greater Than 2 Years

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


3. Perform Conditional Formatting to Calculate Date Greater Than 2 Years

Last but not the least, we will apply Conditional Formatting to give format the working duration of the XYZ employees who are working for more than two years. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cells C5 to C14 for the convenience of our work. After that, from your Home ribbon, go to,

Home → Styles → Conditional Formatting → New Rules

Perform Conditional Formatting to Calculate Date Greater Than 2 Years

  • As a result, a dialog box named New Formatting Rule pops up. From the New Formatting Rule dialog box, firstly, select Use a formula to determine which cells to format. Hence, Type the below formula in the Format values where this formula is true:. The formula is,
=C5<=TODAY()-730
  • After that, select the Format option.

  • Further, a Format cells dialog box will appear in front of you. From the Format cells dialog box, firstly, select the Fill Hence, select a color you like. We choose light Orange. At last, press the OK option.

Perform Conditional Formatting to Calculate Date Greater Than 2 Years

  • After that, you will back to the New Formatting Rule dialog box. From the New Formatting Rule dialog box, again press the OK option.

  • After completing the above process, you will be able to give format with light Orange who are working at the XYZ company and the duration of work is greater than 2 years which has been given in the below screenshot.

Perform Conditional Formatting to Calculate Date Greater Than 2 Years

Read More: Excel Formula If One Date is Greater Than Another Date


Things to Remember

➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.

#DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.


Conclusion

I hope all of the suitable methods mentioned above to calculate a date using Excel formula if date is greater than 2 years from another date will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo