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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

Let’s assume we have an Excel worksheet that contains information about several sales representatives of XYZ Group. The name of the sales representatives, the Joining Date, and the 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, and IF functions, and Conditional Formatting. We can easily apply the DATEDIF, IF functions, and Conditional Formatting in Excel to calculate the working duration using an Excel formula if the 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 the DATEDIF Function to Return a Specific Text If a 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 have been 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


2. Apply the IF Function to Return a Specific Text If a 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 for 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


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

Last but not least, we will apply Conditional Formatting to give format the working duration of the XYZ employees who have been 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 chose 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


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.


Download Practice Workbook

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


Conclusion

I hope all of the suitable methods mentioned above to calculate a date using the Excel formulas if a 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.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo