Last week I was presenting a workshop on the date and time functions in Microsoft® Excel®. One of the delegates, a Human Resource professional, asked me how they can calculate the age of an employee in Excel. I suggested they use the DATEDIF or the DAYS360 functions. For the sake of other HR professionals who weren’t in the workshop last week, I will demonstrate how this can be achieved. However, since we have covered the DATEDIF function in a previous tip, I will show you how to use the Days360 function.
The Days360 function calculates the number of days between two dates in a 360-day year (twelve 30-day months), but it can also be used to calculate a person’s age.
Note: Download the sample workbook to practice this exercise.
Applies To: Microsoft® Excel®2010, 2013
- Open the sample workbook.
- To calculate the age of an individual.
- Select cell E3.
- Type =DAYS360(
- Click on cell C3 for the start date
- Add a comma and click on cell D3 for the end date
- Add the closing bracket: ).
- Then type /360
- Copy the formula down to cell E7.
- The ages are given as per screen shot below.
In this example cell C3 refers to the start date (date of birth) and D3 to the current date. We divide the answer by 360 days because we want to convert the days to years.