# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Formula to subtract a date from current date

## Fidelio1st

Hi, I'm creating a database of ages on people.  What I need is to create a formula that will tell me how old they are on the current day; i.e. if a Person A's birthday is 2/27/80, then the Age Column will read "25" today (2/26/06).  But when I open the file tomorrow (2/27/06) it will change to "26".

I.e. I don't want to manually have to update the Age Column.  Is there a formula to do this?

So far I've come up with Column A=Birthday, Column B=Today's date (with the formula "TODAY()") and when I subtract A from B, it gives me a year, where the age is the last 2 digits.  How can I take that number and convert it into the person's age?

Ex. 2/26/2006 - 8/18/1967 = 7/11/38. While the 7/11 is superfulous, 38 is the age.  But how do I convert that number to state that?

----------


## davesexcel

=ROUND((B15-A15)/365.25,0)
just subtract the two cells and round it to the nearest whole number
Dave

----------


## Biff

Hi!

Try this:

A1 = birth date
B1 = =TODAY()

=DATEDIF(A1,B1,"y")

Biff

"Fidelio1st" <Fidelio1st.23vb6m_1141009803.2853@excelforum-nospam.com> wrote
in message news:Fidelio1st.23vb6m_1141009803.2853@excelforum-nospam.com...
>
> Hi, I'm creating a database of ages on people.  What I need is to create
> a formula that will tell me how old they are on the current day; i.e. if
> a Person A's birthday is 2/27/80, then the Age Column will read "25"
> today (2/26/06).  But when I open the file tomorrow (2/27/06) it will
> change to "26".
>
> I.e. I don't want to manually have to update the Age Column.  Is there
> a formula to do this?
>
> So far I've come up with Column A=Birthday, Column B=Today's date (with
> the formula "TODAY()") and when I subtract A from B, it gives me a year,
> where the age is the last 2 digits.  How can I take that number and
> convert it into the person's age?
>
> Ex. 2/26/2006 - 8/18/1967 = 7/11/38. While the 7/11 is superfulous, 38
> is the age.  But how do I convert that number to state that?
>
>
> --
> Fidelio1st
> ------------------------------------------------------------------------
> Fidelio1st's Profile:
> http://www.excelforum.com/member.php...o&userid=31956
> View this thread: http://www.excelforum.com/showthread...hreadid=516761
>

----------


## Ryan Poth

Fidelio,

There is an undocumented Excel function called "DATEDIF" which will help you
out here. Try:

=DATEDIF(A1,TODAY(),"y")
where A1 contains the persons birthdate.

For more info on this function, have a look at Chip Pearson's topic:

http://www.cpearson.com/excel/datedif.htm

HTH,
Ryan

"Fidelio1st" wrote:

>
> Hi, I'm creating a database of ages on people.  What I need is to create
> a formula that will tell me how old they are on the current day; i.e. if
> a Person A's birthday is 2/27/80, then the Age Column will read "25"
> today (2/26/06).  But when I open the file tomorrow (2/27/06) it will
> change to "26".
>
> I.e. I don't want to manually have to update the Age Column.  Is there
> a formula to do this?
>
> So far I've come up with Column A=Birthday, Column B=Today's date (with
> the formula "TODAY()") and when I subtract A from B, it gives me a year,
> where the age is the last 2 digits.  How can I take that number and
> convert it into the person's age?
>
> Ex. 2/26/2006 - 8/18/1967 = 7/11/38. While the 7/11 is superfulous, 38
> is the age.  But how do I convert that number to state that?
>
>
> --
> Fidelio1st
> ------------------------------------------------------------------------
> Fidelio1st's Profile: http://www.excelforum.com/member.php...o&userid=31956
> View this thread: http://www.excelforum.com/showthread...hreadid=516761
>
>

----------

