Working Out a Person's Age in Excel
※ Download: Age calculation formula in excel
Increase your productivity in 5 minutes. Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel can help you calculate the age of a person in different ways. But if you are going to do it, you might as well do it right! Enter the birthdays using a common format.
Excel generally assumes that format indicates a date, but if for some reason your cell format is specified as something else, then it is simple to change your call value into a date using the date format available for the cell. As the result, you get the patient's age as of January 1 of a particular year: Find out a date when a person attains N years of age Supposing your friend was born on 8 March 1978.
How to Calculate Age with an Excel Formula - They normally need a bit of working out!
This returns the number of years rounded down. See below for examples and an explanation of other formulas having to do with calculating age in Excel. Although dates cannot be earlier than 1900, they can either be date values or text. How it works: It subtracts the years and then subtracts 1 if the month and day of the end date comes before the month and day of the start date. In Excel, the numeric value for TRUE is 1. Method 3: Return a Decimal Number of Years Between Two Dates The exact number of days in a year is 365. To calculate age using this method, first subtract the dates to calculate the number of days, then divide by 365. Calculate Age in Years, Months and Days Ages are often represented using the format 5y 11m 3d or 5 years 11 months and 3 days. There are multiple methods for calculating the age as a combination of years, months and days, but not all methods give the same answers. The complications and differences come from how we treat months with different numbers of days in them. For example, if you subtract a month from 31-Mar-2017, should it be 28-Feb-2017? However, if we use the DATE year, month-1, day method, the result would be 3-Mar-2017. The point is that there may be multiple right answers, depending on the methodology. Step 4: Concatenate the results from steps 1-3 The formula below returns a text string that looks like 49y 11m 6d. This fact makes it a very useful methodology because the process is reversible information is not lost. Method 2: The 30-Day Month Technique The 30-Day Month method is a technique I learned from doing genealogy work. It is based on the procedure of subtracting dates and ages by hand using the technique of first writing dates and ages as YYYYMMDD. In elementary school, you learned that when subtracting you can borrow from the tens or hundreds place when needed. Here, if we need to borrow from the MM place, we subtract one from MM and then add 30 days to DD. If we need to borrow from the YYYY place, we subtract one from the YYYY place and add 12 to MM. Step 1: Subtract the days in the DD place, borrowing 30 days from the MM place if needed. Excel may try to convert the result to a date, so change the number format back to General. Step 2: Subtract the months in the MM place, borrowing 12 months from the YY place if needed. How it works: Start with the month of the end date, then subtract 1 if you had to borrow 30 days, then add 12 months if you need to borrow from the years, then subtract the month of the start date. Step 3: Subtract the years in the YYYY place. How it works: Start with the year of the end date, then subtract 1 if you previously had to borrow 12 months, then subtract the year of the start date. Method 2 results in a different age than Method 1 about 32% of the time, but only by 2 days at most. Turns out that both methods return the exact same number of years and months. It is only the number of days that may be different. Calculate the Birthdate if you Know the Death Date and Age Use DATE to subtract a combination of years, months and days from a date. Whether the answer is correct depends upon the methodology used to calculate the age. Calculate the Death Date if you Know the Birth Date and Age Use DATE to add a combination of years, months and days to a date. Whether the answer is correct depends upon the methodology used to calculate the age. If you enter a date such as 28-Aug-2017 in Excel, it will store that as the number 42975, but if you enter a date prior to 1900, it will store the date as text. Below are a couple of methods for calculating age in Excel when working with dates prior to 1900. Method 1: Add 2000 years then use the formulas above For example, use the formula below to calculate the age when the birth date is 28-Aug-1803 and death date is 28-Aug-1850. Plus, it's easy to add and subtract 2000 years in your head. Method 2: Create a User-Defined Function based on VBA date functions The VBA date functions can handle all valid Gregorian dates. Method 3: Calculate age using the 30-Day Month Method and YYYYMMDD date values If you enter dates as numeric values in the format YYYYMMDD, then you can use the 30-day month method also known as the 8870 technique to subtract the dates. The result will be the age in YYMMDD format. The work-around formula recommended by Microsoft on their support page is wrong even more often. There are two methods for calculating the remaining days, shown below. Method 1 is the method I think DATEDIF uses, but it can lead to negative values that are clearly wrong. It uses DATE 2015,1+1,31 which returns the date 3-Mar-2015. If the end date is 1-Mar-2015, the result will end up being -2. But, I also wanted to delve deep to address some of the intracacies of other methods. The spreadsheet I set up to compare these various methods analyzed 100's of thousands of date combinations. That is how I came up with examples where some of these methods produce errors and how I checked to make sure the alternative formulas are or are not 100% correct. The YEARFRAC and division by 365. If you have any questions pertaining to calculating age or these particular formulas, or see any typos, you are welcome to comment below or email me. See our to learn more. Save my name, email, and website in this browser for the next time I comment.
Calculate the Death Date if you Know the Birth Date and Age Use DATE to add a combination of years, months and days to a date. It is always difficult and confusing to find out the actual age in days, months and sometimes in years. This example uses the, and. Excel generally assumes that format indicates a date, but if for some reason your cell format is specified as something else, then it is simple to change your call value into a date using the date format available for the cell. Also, because the way this formula calculates, if a person was born in a leap year and today is their birthday it doesn't get their age right until tomorrow. If you want to know the exact age, i.
