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.
