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.

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,

• 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.

• 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.

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

Similar Readings

• How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)
• If Cell Contains Date Then Return Value in Excel (5 Examples)
• Conditional Formatting Based on Date Older Than 1 Year in Excel
• Excel Conditional Formatting for Date Within 3 Months (3 Methods)

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,

• 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.

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.

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

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

• 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.

• 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.

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.