Greetings

I am trying to create a simple spreadsheet that I can input a few dates of
important recurring events into (birthdays, anniversaries etc), and excel
will display how long until the next annual occurrence of this event and how
many years it celebrates.

For example, if I have someone's birthday input as 1 November 1984 and I
open the spreadsheet today, I would like to have excel display that there
are 64 days until their birthday and that they will be 20 years old.

Is this possible?

Many thanks for any help.

Regards

Mat

Mat

Please do not cross-post. Just fragments answers. Most of the regulars here
monitor all groups.

In answer to your question.....

DATEDIF was described only in Excel 2000 but is available in many versions of
Excel, including 2000.

You do not need any add-ins, should work with normal setup.


=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " &
DATEDIF(A1,NOW(),"md") & " days"

The above formula will return a string like 42 years, 9 months, 26 days

A1 holds the earliest date.

For more on DATEDIF see Chip Pearson's site.

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

There are some caveats with DATEDIF.

From a posting by John McGimpsey.........................

However, be very careful about days - DATEDIF() assumes that a month is
as long as the number of days in the first date's month, so if

A1 = 31 January 1980

on 1 March 2005, the result will be:

Age is 25 Years, 1 Months and -2 Days

Some people may not feel -2 days is valid.

Gord Dibben Excel MVP

On Sun, 29 Aug 2004 20:06:04 +0100, "Mat"
<(E-Mail Removed)> wrote:

>Greetings
>
>I am trying to create a simple spreadsheet that I can input a few dates of
>important recurring events into (birthdays, anniversaries etc), and excel
>will display how long until the next annual occurrence of this event and how
>many years it celebrates.
>
>For example, if I have someone's birthday input as 1 November 1984 and I
>open the spreadsheet today, I would like to have excel display that there
>are 64 days until their birthday and that they will be 20 years old.
>
>Is this possible?
>
>Many thanks for any help.
>
>Regards
>
>Mat
>

> The above formula will return a string like 42 years, 9 months, 26 days

Happy 43rd birthday in 66 days time Gord! <g>

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> Mat
>
> Please do not cross-post. Just fragments answers. Most of the regulars
here
> monitor all groups.
>
> In answer to your question.....
>
> DATEDIF was described only in Excel 2000 but is available in many versions
of
> Excel, including 2000.
>
> You do not need any add-ins, should work with normal setup.
>
>
> =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, "
&
> DATEDIF(A1,NOW(),"md") & " days"
>
> The above formula will return a string like 42 years, 9 months, 26 days
>
> A1 holds the earliest date.
>
> For more on DATEDIF see Chip Pearson's site.
>
> http://www.cpearson.com/excel/datedif.htm
>
> There are some caveats with DATEDIF.
>
> From a posting by John McGimpsey.........................
>
> However, be very careful about days - DATEDIF() assumes that a month is
> as long as the number of days in the first date's month, so if
>
> A1 = 31 January 1980
>
> on 1 March 2005, the result will be:
>
> Age is 25 Years, 1 Months and -2 Days
>
> Some people may not feel -2 days is valid.
>
> Gord Dibben Excel MVP
>
> On Sun, 29 Aug 2004 20:06:04 +0100, "Mat"
> <(E-Mail Removed)> wrote:
>
> >Greetings
> >
> >I am trying to create a simple spreadsheet that I can input a few dates
of
> >important recurring events into (birthdays, anniversaries etc), and excel
> >will display how long until the next annual occurrence of this event and
how
> >many years it celebrates.
> >
> >For example, if I have someone's birthday input as 1 November 1984 and I
> >open the spreadsheet today, I would like to have excel display that there
> >are 64 days until their birthday and that they will be 20 years old.
> >
> >Is this possible?
> >
> >Many thanks for any help.
> >
> >Regards
> >
> >Mat
> >
>

Don't I wish!

You're short by close to 20 years.

Gord

On Sun, 29 Aug 2004 20:55:10 +0100, "Sandy Mann" <(E-Mail Removed)>
wrote:

>> The above formula will return a string like 42 years, 9 months, 26 days
>
>Happy 43rd birthday in 66 days time Gord! <g>
>
>Sandy

Thanks for the reply. Unfortunately I am running Office XP, any ideas how to
do it in this?

Kind regards

Mat


"Sandy Mann" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> > The above formula will return a string like 42 years, 9 months, 26 days
>
> Happy 43rd birthday in 66 days time Gord! <g>
>
> Sandy
>
> --
> to e-mail direct replace @mailintor.com with @tiscali.co.uk
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:(E-Mail Removed)...
> > Mat
> >
> > Please do not cross-post. Just fragments answers. Most of the regulars
> here
> > monitor all groups.
> >
> > In answer to your question.....
> >
> > DATEDIF was described only in Excel 2000 but is available in many
versions
> of
> > Excel, including 2000.
> >
> > You do not need any add-ins, should work with normal setup.
> >
> >
> > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months,
"
> &
> > DATEDIF(A1,NOW(),"md") & " days"
> >
> > The above formula will return a string like 42 years, 9 months, 26 days
> >
> > A1 holds the earliest date.
> >
> > For more on DATEDIF see Chip Pearson's site.
> >
> > http://www.cpearson.com/excel/datedif.htm
> >
> > There are some caveats with DATEDIF.
> >
> > From a posting by John McGimpsey.........................
> >
> > However, be very careful about days - DATEDIF() assumes that a month is
> > as long as the number of days in the first date's month, so if
> >
> > A1 = 31 January 1980
> >
> > on 1 March 2005, the result will be:
> >
> > Age is 25 Years, 1 Months and -2 Days
> >
> > Some people may not feel -2 days is valid.
> >
> > Gord Dibben Excel MVP
> >
> > On Sun, 29 Aug 2004 20:06:04 +0100, "Mat"
> > <(E-Mail Removed)> wrote:
> >
> > >Greetings
> > >
> > >I am trying to create a simple spreadsheet that I can input a few dates
> of
> > >important recurring events into (birthdays, anniversaries etc), and
excel
> > >will display how long until the next annual occurrence of this event
and
> how
> > >many years it celebrates.
> > >
> > >For example, if I have someone's birthday input as 1 November 1984 and
I
> > >open the spreadsheet today, I would like to have excel display that
there
> > >are 64 days until their birthday and that they will be 20 years old.
> > >
> > >Is this possible?
> > >
> > >Many thanks for any help.
> > >
> > >Regards
> > >
> > >Mat
> > >
> >
>
>

As you were...

Just noticed from the Pearson site about it being undocumented, but found
out how to use it.

Many thanks for the assistance.


"Mat" <(E-Mail Removed)> wrote in message
news:cgtnm4$j2m$(E-Mail Removed)...
> Thanks for the reply. Unfortunately I am running Office XP, any ideas how
to
> do it in this?
>
> Kind regards
>
> Mat
>
>
> "Sandy Mann" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > > The above formula will return a string like 42 years, 9 months, 26
days
> >
> > Happy 43rd birthday in 66 days time Gord! <g>
> >
> > Sandy
> >
> > --
> > to e-mail direct replace @mailintor.com with @tiscali.co.uk
> >
> > "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> > news:(E-Mail Removed)...
> > > Mat
> > >
> > > Please do not cross-post. Just fragments answers. Most of the
regulars
> > here
> > > monitor all groups.
> > >
> > > In answer to your question.....
> > >
> > > DATEDIF was described only in Excel 2000 but is available in many
> versions
> > of
> > > Excel, including 2000.
> > >
> > > You do not need any add-ins, should work with normal setup.
> > >
> > >
> > > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
months,
> "
> > &
> > > DATEDIF(A1,NOW(),"md") & " days"
> > >
> > > The above formula will return a string like 42 years, 9 months, 26
days
> > >
> > > A1 holds the earliest date.
> > >
> > > For more on DATEDIF see Chip Pearson's site.
> > >
> > > http://www.cpearson.com/excel/datedif.htm
> > >
> > > There are some caveats with DATEDIF.
> > >
> > > From a posting by John McGimpsey.........................
> > >
> > > However, be very careful about days - DATEDIF() assumes that a month
is
> > > as long as the number of days in the first date's month, so if
> > >
> > > A1 = 31 January 1980
> > >
> > > on 1 March 2005, the result will be:
> > >
> > > Age is 25 Years, 1 Months and -2 Days
> > >
> > > Some people may not feel -2 days is valid.
> > >
> > > Gord Dibben Excel MVP
> > >
> > > On Sun, 29 Aug 2004 20:06:04 +0100, "Mat"
> > > <(E-Mail Removed)> wrote:
> > >
> > > >Greetings
> > > >
> > > >I am trying to create a simple spreadsheet that I can input a few
dates
> > of
> > > >important recurring events into (birthdays, anniversaries etc), and
> excel
> > > >will display how long until the next annual occurrence of this event
> and
> > how
> > > >many years it celebrates.
> > > >
> > > >For example, if I have someone's birthday input as 1 November 1984
and
> I
> > > >open the spreadsheet today, I would like to have excel display that
> there
> > > >are 64 days until their birthday and that they will be 20 years old.
> > > >
> > > >Is this possible?
> > > >
> > > >Many thanks for any help.
> > > >
> > > >Regards
> > > >
> > > >Mat
> > > >
> > >
> >
> >
>
>