software

Hi,

I am setting up a spreadsheet to calculate people's age and the number of days
to their next birthday. Calculating their age I can do but calculating the
number of days until their next birthday is proving a little difficult.

I am currently using the formula =365-DATEDIF($C3,TODAY(),quot;ydquot;) where the full
dd/mm/yyyy birth date is in c3.

It occurred to me that the formula will be correct for non leap years but will
probably be one day out on leap years.

I would welcome any suggestions on how to correct the above formula to
compensate for leap years?
--

Cheers . . . JC

=DATE(YEAR(TODAY()) IF(TEXT(TODAY(),quot;mmddquot;)gt;TEXT(C 3,quot;mmddquot;),1,0),MONTH(C3),DAY(C3))-TODAY()

Works OK for leap years, depending on what your definition is of the
birthdays in non-leap years for those born on Feb. 29

--
Kind regards,

Niek Otten

quot;JCquot; gt; wrote in message
...
gt; Hi,
gt;
gt; I am setting up a spreadsheet to calculate people's age and the number of
gt; days
gt; to their next birthday. Calculating their age I can do but calculating
gt; the
gt; number of days until their next birthday is proving a little difficult.
gt;
gt; I am currently using the formula =365-DATEDIF($C3,TODAY(),quot;ydquot;) where the
gt; full
gt; dd/mm/yyyy birth date is in c3.
gt;
gt; It occurred to me that the formula will be correct for non leap years but
gt; will
gt; probably be one day out on leap years.
gt;
gt; I would welcome any suggestions on how to correct the above formula to
gt; compensate for leap years?
gt; --
gt;
gt; Cheers . . . JC
Hi Niek,

It took a little while to puzzle your formula out but I now understand it. Many
thanks for your assistance.

I ran a few tests and found that it gives the correct answer if both birthdate
and today() are in non leap years, if both birthdate and today() are in leap
years or when the birthdate is in a non leap year and today() is a leap year.

When the birthdate is in a leap year and today() is in a non leap year it is
still giving correct answers except when the birthdate is 29th February. If
the birthdate is 29th February it calculates as if the non leap year birthday is
1st March. Thus on the 25th February, 2006 for a 29th February 1996 birthdate
it calculates that the next birthday will be in 4 days time.

I now understand what you meant when you wrote quot;Works OK for leap years,
depending on what your definition is of the birthdays in non-leap years for
those born on Feb. 29quot;.

I have no experience with this - are birthdays for those born on 29th February
celebrated on 28th February or 1st March in non leap years?

JCOn Sat, 25 Feb 2006 10:49:14 0100, quot;Niek Ottenquot; gt; wrote:

gt;=DATE(YEAR(TODAY()) IF(TEXT(TODAY(),quot;mmddquot;)gt;TEXT( C3,quot;mmddquot;),1,0),MONTH(C3),DAY(C3))-TODAY()
gt;
gt;Works OK for leap years, depending on what your definition is of the
gt;birthdays in non-leap years for those born on Feb. 29

quot;JCquot; gt; wrote in message
...
gt;gt; Hi,
gt;gt;
gt;gt; I am setting up a spreadsheet to calculate people's age and the number of
gt;gt; days to their next birthday. Calculating their age I can do but calculating
gt;gt; the number of days until their next birthday is proving a little difficult.
gt;gt;
gt;gt; I am currently using the formula =365-DATEDIF($C3,TODAY(),quot;ydquot;) where the
gt;gt; full dd/mm/yyyy birth date is in c3.
gt;gt;
gt;gt; It occurred to me that the formula will be correct for non leap years but
gt;gt; will probably be one day out on leap years.
gt;gt;
gt;gt; I would welcome any suggestions on how to correct the above formula to
gt;gt; compensate for leap years?lt;I have no experience with this - are birthdays for those born on 29th
February
celebrated on 28th February or 1st March in non leap years?gt;

This is what Wikipedia tells us:

A person who was born on 29 February may be called a quot;leaplingquot;. In non-leap
years they usually celebrate their birthday on 28 February or 1 March.--
Kind regards,

Niek Ottenquot;JCquot; gt; wrote in message
...
gt; Hi Niek,
gt;
gt; It took a little while to puzzle your formula out but I now understand it.
gt; Many
gt; thanks for your assistance.
gt;
gt; I ran a few tests and found that it gives the correct answer if both
gt; birthdate
gt; and today() are in non leap years, if both birthdate and today() are in
gt; leap
gt; years or when the birthdate is in a non leap year and today() is a leap
gt; year.
gt;
gt; When the birthdate is in a leap year and today() is in a non leap year it
gt; is
gt; still giving correct answers except when the birthdate is 29th February.
gt; If
gt; the birthdate is 29th February it calculates as if the non leap year
gt; birthday is
gt; 1st March. Thus on the 25th February, 2006 for a 29th February 1996
gt; birthdate
gt; it calculates that the next birthday will be in 4 days time.
gt;
gt; I now understand what you meant when you wrote quot;Works OK for leap years,
gt; depending on what your definition is of the birthdays in non-leap years
gt; for
gt; those born on Feb. 29quot;.
gt;
gt; I have no experience with this - are birthdays for those born on 29th
gt; February
gt; celebrated on 28th February or 1st March in non leap years?
gt;
gt; JC
gt;
gt;
gt; On Sat, 25 Feb 2006 10:49:14 0100, quot;Niek Ottenquot; gt;
gt; wrote:
gt;
gt;gt;=DATE(YEAR(TODAY()) IF(TEXT(TODAY(),quot;mmddquot;)gt;TEXT (C3,quot;mmddquot;),1,0),MONTH(C3),DAY(C3))-TODAY()
gt;gt;
gt;gt;Works OK for leap years, depending on what your definition is of the
gt;gt;birthdays in non-leap years for those born on Feb. 29
gt;
gt; quot;JCquot; gt; wrote in message
gt; ...
gt;gt;gt; Hi,
gt;gt;gt;
gt;gt;gt; I am setting up a spreadsheet to calculate people's age and the number
gt;gt;gt; of
gt;gt;gt; days to their next birthday. Calculating their age I can do but
gt;gt;gt; calculating
gt;gt;gt; the number of days until their next birthday is proving a little
gt;gt;gt; difficult.
gt;gt;gt;
gt;gt;gt; I am currently using the formula =365-DATEDIF($C3,TODAY(),quot;ydquot;) where
gt;gt;gt; the
gt;gt;gt; full dd/mm/yyyy birth date is in c3.
gt;gt;gt;
gt;gt;gt; It occurred to me that the formula will be correct for non leap years
gt;gt;gt; but
gt;gt;gt; will probably be one day out on leap years.
gt;gt;gt;
gt;gt;gt; I would welcome any suggestions on how to correct the above formula to
gt;gt;gt; compensate for leap years?
gt;

If you want to assume that leapling birthdays are celebrated on 28th feb
in non leap years then

=IF(TEXT(C3,quot;ddmmquot;)lt;gt;TEXT(NOW(),quot;ddmmquot;),EDATE(C3,( DATEDIF(C3,NOW(),quot;yquot;) 1)*12)-TODAY(),0)

EDATE is part of Analysis ToolPak add-in--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=516469
As fate would have it, I was attempting to do basically the same thing
as JC was asking for. So I stole that formula for my use, but Excel
clanged up a dialog box with a change in it. It replaced a comma with a
*. I have no clue as to why it would do this but it still seems to work
anyway. If I can, I'll copy and paste Excel's dialog box formula, so
here goes:
=DATE(YEAR(TODAY()) IF(TEXT(TODAY()quot;quot;mmddquot;quot;)gt;TEXT( C
3,quot;quot;mmddquot;quot;)*1,0),MONTH(C3),DAY(C3))-TODAY()
For what it is worth;
That quot;starquot; falls in place between the last quot;quot;mmddquot;quot;) and the 1,0).
So far it hasn't seemed to throw a wrench into the job but I guess time
will tell.--
lsmft
------------------------------------------------------------------------
lsmft's Profile: www.excelforum.com/member.php...oamp;userid=30678
View this thread: www.excelforum.com/showthread...hreadid=516469The formula is correct. Do you happen to have a system where the list
separator is a semicolon ( instead of a comma (,)?

--
Kind regards,

Niek Ottenquot;lsmftquot; gt; wrote in message
...
gt;
gt; As fate would have it, I was attempting to do basically the same thing
gt; as JC was asking for. So I stole that formula for my use, but Excel
gt; clanged up a dialog box with a change in it. It replaced a comma with a
gt; *. I have no clue as to why it would do this but it still seems to work
gt; anyway. If I can, I'll copy and paste Excel's dialog box formula, so
gt; here goes:
gt; =DATE(YEAR(TODAY()) IF(TEXT(TODAY()quot;quot;mmddquot;quot;)gt;TEXT( C
gt; 3,quot;quot;mmddquot;quot;)*1,0),MONTH(C3),DAY(C3))-TODAY()
gt; For what it is worth;
gt; That quot;starquot; falls in place between the last quot;quot;mmddquot;quot;) and the 1,0).
gt; So far it hasn't seemed to throw a wrench into the job but I guess time
gt; will tell.
gt;
gt;
gt; --
gt; lsmft
gt; ------------------------------------------------------------------------
gt; lsmft's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30678
gt; View this thread: www.excelforum.com/showthread...hreadid=516469
gt;
JC gt; wrote
gt; I have no experience with this - are birthdays for those born on 29th
gt; February celebrated on 28th February or 1st March in non leap years?Such people will usually only celebrate their birthday every 4 years. The
disadvantages (getting fewer presents) is greatly outweighed by the
advantage of surviving more than 300 normal years.--
Marc.

On Sat, 25 Feb 2006 08:32:10 -0600, lsmft
gt; wrote:

gt;
gt;As fate would have it, I was attempting to do basically the same thing
gt;as JC was asking for. So I stole that formula for my use, but Excel
gt;clanged up a dialog box with a change in it. It replaced a comma with a
gt;*. I have no clue as to why it would do this but it still seems to work
gt;anyway. If I can, I'll copy and paste Excel's dialog box formula, so
gt;here goes:
=DATE(YEAR(TODAY()) IF(TEXT(TODAY()quot;quot;mmddquot;quot;)gt;TEXT( C3,quot;quot;mmddquot;quot;)*1,0),MONTH(C3),DAY(C3))-TODAY()
gt;For what it is worth;
gt;That quot;starquot; falls in place between the last quot;quot;mmddquot;quot;) and the 1,0).
gt;So far it hasn't seemed to throw a wrench into the job but I guess time
gt;will tell.The correct formula is
=DATE(YEAR(TODAY()) IF(TEXT(TODAY(),quot;mmddquot;)gt;TEXT(C 3,quot;mmddquot;),1,0),MONTH(C3),DAY(C3))-TODAY()

You will note a few differences between it and your one - the use of one pair of
'double quotes' (i.e. quot;mmddquot; not quot;quot;mmddquot;quot;) and a comma after TEXT(TODAY()
and where your * is placed.

Try copying the formula from this message and pasting it into your cell in
Excel. You may have to make corrections for the column name and row number - I
have my spreadsheet set out as

Column Data
A Last name
B First name
C Birth date
D Calculated age
E Days to next birthday

I added a note re the assumption that the next birthday for those born on 29th
Feb is 1st March in non leap years.

I hope that this helps.
--

Cheers . . . JC


Hi JC,

What do you expect the formula to return on 1st March (in a non leap
year) for a 29th February birthdate?

btw you could simpify to

=DATE(YEAR(TODAY()) (TEXT(TODAY(),quot;mmddquot;)gt;TEXT(C3, quot;mmddquot;)),MONTH(C3),DAY(C3))-TODAY()--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=516469