Calculate age

Excel can help you calculate the age of a person in different ways. The table below shows common methods to do so, 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. Open a new worksheet, the right-click cell A1 and choose 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))

The result is the age of person—the difference between today and the birthdate in A2.

This example uses the YEAR and NOW functions.

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

=YEAR(NOW())-1960

The age of a person born in 1960, without using cell references.

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

=YEARFRAC(A3,A5)

Calculates the year-fractional age between the dates in A5 and A3.

=(A5-A6)/365.25

Calculates the age between the dates in A5 and A6, which is 12.08.

To account for a leap year occurring every 4 years, 365.25 is used in the formula.

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

Calculates the number of days between two dates without using cell references, which is 153.

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

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)

The number of months between A3 and the current date.

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

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

=NETWORKDAYS(A3,A2,A3:A5)

The number of whole working days between the two dates in A2 and A3, which is 107. 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(A2,A3,TRUE)

The number of days between the two dates in A2 and A3, which is 570. This is based on a 360-day year (twelve 30-day months) that are typical in accounting calculations.

This example uses the DAYS360 function.

=EDATE(A3,-4)

Convert this to a date format and this should be 1/2/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

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×