Using the DATEDIF function
DATEDIF is a function that works out the age of the employee to display the year, month, and day in the cell. When working with the DATEDIF function, we will need to type the function into the cell to construct it from scratch. The DATEDIF function is not resident in the drop-down list of functions in Excel, and neither is the tooltip to guide you when constructing arguments. We will need to remember the function and construction arguments. Proceed as follows:
- Using the same workbook as prior topics, Date and Time.xlsx, make sure you are on the EMPLOYEES worksheet.
-
Employee age is calculated in years in column H. If we wanted to work out the year, month, or day instead, we could need to use a different function. Let’s calculate this in column J of the worksheet.
-
Click on cell J5 to construct the formula. We will be using the DATEDIF
function for this example. The DATEDIF function syntax is shown here:
=DATEDIF(start_date,end_date,unit). We will build this formula using different units to achieve the result.
-
Type =DATEDIF( then click on G5 as this is our start_date value (birth date). Add a comma to separate the arguments, then type TODAY() as the end_date value as we are working out how old the employee is today. Add a comma again to specify the last part of the formula.
To find out more about the DATEDIF function, click on the blue DATEDIF link in the syntax, as shown here:
Figure 11.17 – The DATEDIF function
- The first unit we require is the year, so we need to enter Y in the formula as the unit. Close the function bracket by adding ).
-
As we would like to see the word years after the year unit, we need to include
” years ” after the function to expand the text string. Press the spacebar, then add another &.
- Copy the first DATEDIF function and amend the unit to reflect “YM”.
-
Add & to expand on the formula, followed by the spacebar, then include
” months ” after the function to expand the text string. Your formula should look exactly like this: =DATEDIF(G5,TODAY(),”Y”)&” years “
&DATEDIF(G5,TODAY(),”YM”)& ” months”. You can see this displayed in the following screenshot:
Figure 11.18 – Formula to work out the age of employees
- Press Enter to see the result and copy it down to the rest of the employees, as illustrated here:
Figure 11.19 – Employee age in years and months
You have now learned to apply the YEARFRAC and DATEDIF functions. In the next section, we will look at how to calculate retirement dates and the remaining retirement years for employees.
Returning the date of retirement
Let’s work out the exact date our employees will retire using the EDATE function. Our employees reach retirement at 65. Follow the next steps:
-
Locate cell O5 on the EMPLOYEES worksheet. We will use the birth date of our employees to work out the date on which they will retire when reaching 65 years old. The syntax for the EDATE function is =EDATE(start_date,months).
- Start the formula construction by entering the formula =EDATE(.
-
Click to select cell G5, which contains the first employee’s birth date. Press the
comma on the keyboard to separate the arguments.
- Type 12 for the months of the year multiplied by 65—that is, 12*65.
- Press Enter to see the result for the first employee. Notice that a result is a serial number. Format the serial number in cell O5 to a short date. Autofill to the rest of the employees.
Now that we know how to calculate the actual date of retirement, there may be instances where we need to how many years until the employee reaches the retirement year. Let’s work through this in the next section.
Working out years to retirement
To calculate the number of years to retirement, we can use the YEARFRAC function. In cell O5 of the worksheet, we have generated the retirement date. We will use the retirement date to aid our calculation. Proceed as follows:
-
Construct the formula in cell P5 of the worksheet. Type =YEARFRAC( then add the
TODAY() function.
- Add a comma to separate the arguments.
- Click on cell O2 to include the actual retirement date of the employee, then enclose the formula with an ending ), as illustrated in the following screenshot:
Figure 11.20 – Working out the number of years till retirement
- Press Enter to see the result.
- Amend the formula to include the INT function so that it returns just the integer, like so: =INT(YEARFRAC(TODAY(),O5)).
- Note that to calculate the last day of the retirement month, you would need to construct the following formula: =IF(DAY(G5)=1,DATE(YEAR(G5)+60, MONTH(G5),0),DATE(YEAR(G5)+60,MONTH(G5)+1,0)). You can see this formula in the following screenshot:
Figure 11.21 – Working out the last day of retirement
-
Once you have formulated the function, be sure to change the date format.
In the next topic, we will find the number of days between two different dates.
Calculating days between two dates
The DAYS360 function accepts that there are 30 days in a month. Use this function to calculate the number of days between two dates. Proceed as follows:
-
Click to select the EXAMPLES worksheet in the Date and Time.xlsx
workbook.
- In B22 and C22, we have dates entered in the cells. Our task is to find out how many days are between the two dates.
- We will construct the formula in cell D22.
- Type =DAYS360(.
- Click to select the date in B22, then separate the arguments by adding a comma.
- Click to select the date in C22, then press Enter on the keyboard to confirm. The process is illustrated in the following screenshot:
Figure 11.22 – DAYS360 function
-
The day between the two dates is displayed in cell D22. Fill down to the rest of the cells.
Now that we have explored date functions, let’s have a look at some time functions.
