How to Calculate Age Manually In Excel? This is tutorial will shows how to get age from birthday in excel or create age calculator. You will get knowledge of formulas how to calculate age as a number as completer, years, month and days.
actually, here is no special function in excel to calculate Age Manually In Excel, however here is a few different ways to calculate age in excel to convert birthday to age.
How to calculate age manually from date of birth in Excel
In Microsoft excel, you can use a formula to compute an exact age in months, day, hour and minuts. This is way can calculte age manually.
Basic formula to calculate age in years
How to know the out someone’s age? This is simply with subtracting the birthday date from the current date.
Look at the picture. Assuming a birthday is in cell B2, and the formula to calculate age manually for years like :
=(TODAY()-B2)/365
This is formula (TODAY()-B2) return the difference between the curent date and birthday date, then /365 to get numbers of years.
Age Calculator from date of birth with YEARFRAC function
YEARFRAC function that returns the fraction of the year, Syntac of the YEARFRAC function as follows :
YEARFRAC(start_date, end_date, [basis])
To complete the YEARFRAC formula, supply the following values yearfrac function :
- Start_date – date of birth.
- End_date – TODAY() function to return the today’s date.
- Basis – use basis 1 that tells Excel to divide the actual number of days per month by the actual number of days per year.
=YEARFRAC(B2, TODAY(), 1)
To fix the decimal number with 0 on the last, use ROUNDDOWN function.
=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1), 0)
Calculate age in Excel with DATEDIF
The Formula :
DATEDIF(start_date, end_date, unit)
Example the calculuate age manually using this formula :
How to get age from birthday in years, months and days
This is ways to get age from birthday in years, months and days, the following screenshot demonstrates the final calculate age manually.
Using this formula to calculate age :
=IF(DATEDIF(B2, TODAY(),"y")=0,"",DATEDIF(B2, TODAY(),"y")&" years, ")& IF(DATEDIF(B2, TODAY(),"ym")=0,"",DATEDIF(B2, TODAY(),"ym")&" months, ")& IF(DATEDIF(B2, TODAY(),"md")=0,"",DATEDIF(B2, TODAY(),"md")&" days")
