Example 1 – Use the DATEDIF Function to Return a Specific Text If a Date Is Greater Than 2 Years
We will apply the DATEDIF function to calculate the working duration of the employees of XYZ company and use the IF function to identify those employees who have been working with XYZ company for more than 2 years.
Step 1:
- Select cell E5.
- Enter 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.
- Press Enter to get the return of the functions and the return is YES.
Step 2:
- AutoFill the functions to the rest of the cells in column E.
- Highlight the cells that have a return of less than 2 years and fill them with Yellow using the Fill option on the Home ribbon.
Example 2 – Apply the IF Function to Return a Specific Text If a Date Is Greater Than 2 Years
In our sample dataset, we will identify employees who have been working at XYZ company for more than 2 years (730 days) using the IF function.
Step 1:
- Select cell E5.
- Enter the following IF function,
=IF((D5-C5)>730, "YES", "NO")- D5 is the Resigning date and C5 is the Joining date.
- If the output of the D5-C5 is greater than 730, the IF function returns YES otherwise it returns NO.
- Press Enter to get the return of the IF function and the return is NO.
Step 2:
- AutoFill the IF function to the rest of the cells in column E.
- Fill those cells with green whose return is greater than 2 years or 730 days using the Fill option from the Home ribbon.
Example 3 – Perform Conditional Formatting to Calculate Date Greater Than 2 Years
Steps:
- Select cells C5 to C14 and from your Home ribbon, go to,
Home → Styles → Conditional Formatting → New Rules
- A dialog box named New Formatting Rule pops up. From the New Formatting Rule dialog box, select Use a formula to determine which cells to format. Enter the following formula in the Format values where this formula is true:
=C5<=TODAY()-730- Select the Format option.
- A Format cells dialog box will open. Select the Fill and choose a color. We chose light Orange. Press OK.
- From the New Formatting Rule dialog box, press OK.
- As shown in the image below, you can now format the cells with a light orange color for employees who have been working at XYZ company for more than 2 years.
Download Practice Workbook
