The following screenshot demonstrates the final Excel age formula in action - it returns age in years, months, and days, displaying only non-zero values: For this, add 3 IF statements that check for 0's, one per each DATEDIF: The formula works great, but you can improve it even further by hiding zero values. The above formula returns 3 numbers (years, months, and days) concatenated in a single text string, as shown in the screenshot below:ĭoes not make much sense, uh? To make the results more meaningful, separate the numbers with commas and define what each value means: To get the number of days: =DATEDIF(B2,TODAY(),"MD")Īnd then, concatenate the above functions in a single formula, like this:. ![]() To get the number of months: =DATEDIF(B2, TODAY(), "YM").To get the number of years: =DATEDIF(B2, TODAY(), "Y").how many years, months and days there are between someone's birth date and the current date, write 3 different DATEDIF functions: How to get age from birthday in years, months and daysĪs you have just seen, calculating age as the number of full years that the person has lived is easy, but it is not always sufficient. No additional rounding function is needed in this case because a DATEDIF formula with the "y" unit calculates the number of full years: In this example, the DOB is in cell B2, and you reference this cell in your age formula: The syntax of the YEARFRAC function is as follows: the number of whole days between two dates. Calculate age from date of birth with YEARFRAC functionĪ more reliable way to convert DOB to age in Excel is using the YEARFRAC function that returns the fraction of the year, i.e. Further on in this tutorial, you will learn a couple of special functions that calculate age faultlessly regardless of the year. Overall, subtracting the birth date from the current date works great in normal life, but is not the ideal approach in Excel. For example, if you are calculating the age of a child who hasn't yet lived through a leap year, dividing by 365.25 produces a wrong result. However, this approach is not perfect either. For example, if someone was born on February 29 and today is February 28, the formula will make a person one day older.Īs an alternative, you can divide by 365.25 instead of 365 since every fourth year has 366 days. Dividing by the average number of days in a year works fine most of the time, but sometimes it gets the age wrong. To display the number of complete years, use the INT function to round a decimal down to the nearest integer:ĭrawbacks: Using this age formula in Excel produces pretty accurate results, but not flawless. In most cases, it returns a decimal number as demonstrated in the screenshot below. The formula is obvious and easy-to-remember, however, there is a tiny problem. The first part of the formula (TODAY()-B2) returns the difference between the current date and date of birth is days, and then you divide that number by 365 to get the numbers of years. This conventional age formula can also be used in Excel.Īssuming a birthdate is in cell B2, the formula to calculate age in years goes as follows: ![]() How do you normally figure out someone's age? Simply by subtracting the birth date from the current date. But let's be traditional, and learn how to calculate age from DOB in years first. In Microsoft Excel, you can make a formula to compute an exact age in months, days, hours and even minutes. In everyday life, the question " How old are you?" usually implies an answer indicating how many years you have been alive. How to calculate age from date of birth in Excel
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |