Calculate age

There are different ways to calculate a person's age. The table below shows common methods using the date and time functions.

To use these examples in Excel, drag to select the data in the table, then right-click the selection and pick Copy. In a new worksheet, right-click cell A1 and pick Paste Options > Keep Source Formatting.

Data

10/2/2012

5/2/2014

6/3/2014

7/3/2014

6/3/2002

Formula

Description

=(YEAR(NOW())-YEAR(A2))

Gives the age of person, 2 between today's date and the birthdate in A2.

This example uses the YEAR function and NOW function.

If cell doesn't display as a number, make sure it is formatted as a number or General. Learn how to format a cell as a number or date.

=YEAR(NOW())-1960

Gives the age of a person born in 1960 without using cell references.

If cell doesn't display as a number, make sure it is formatted as a number or General. Learn how to format a cell as a number or date.

=YEARFRAC(A3,A5)

Calculates age in fractions, 1.75, between the dates in A5 and A3.

=(A5-A6)/365.25

Calculates age, 12, between the dates in A5 and A6

365.25 is used in the formula to take into account leap year occurring every 4 years.

=("10/2/2014"-"5/2/2014")

Gives the number of days, 153, between two dates without using cell references.

=DAYS(TODAY(),"2/15/79")

Gives the number of days between two dates using two date functions.

The two arguments of the DAYS function can be actual dates, cell references, or another date and time function, such as the TODAY function.

=(YEAR(NOW())-YEAR(A3))*12+MONTH(NOW())-MONTH(A3)

Gives the number of months between A3 and the current date.

This example uses the YEAR function , NOW function, and MONTH function.

When you enter this formula into a cell, make sure you format it as a number. Learn how to format a cell as a number or date.

=NETWORKDAYS(A3,A2,A3:A5)

Gives the number of whole working days, 107, between two dates in A2 and A3. Working days exclude weekends and holidays. The last argument, A3:A5, lists the holidays to be subtracted from the working days.

This example uses the NETWORKDAYS function.

=DAYS360(A3,A2,TRUE)

Gives the number of days, 150, between two dates based on a 360-day year (twelve 30-day months) used in accounting calculations.

This example uses the DAYS360 function.

=EDATE(A3,-4)

Gives 1/4/2014, which is four months (or -4) prior to the date in A3.

This example uses the EDATE function, which is used to calculate maturity dates on banking notes.

Related

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×