Birthday calculations

[JC]

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(),"yd") 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?

 

[Niek Otten]

=DATE(YEAR(TODAY())+IF(TEXT(TODAY(),"mmdd")>TEXT(C3,"mmdd"),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

 

[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 "Works OK for leap years,
depending on what your definition is of the birthdays in non-leap years for
those born on Feb. 29".

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?

JC

 

[Niek Otten]

<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?>

This is what Wikipedia tells us:

A person who was born on 29 February may be called a "leapling". In non-leap
years they usually celebrate their birthday on 28 February or 1 March.

 

[daddylonglegs]

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

=IF(TEXT(C3,"ddmm")<>TEXT(NOW(),"ddmm"),EDATE(C3,(DATEDIF(C3,NOW(),"y")+1)*12)-TODAY(),0)

EDATE is part of Analysis ToolPak add-i

 

[lsmft]

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()""mmdd"")>TEXT(C
3,""mmdd"")*1,0),MONTH(C3),DAY(C3))-TODAY()
For what it is worth;
That "star" falls in place between the last ""mmdd"") and the 1,0).
So far it hasn't seemed to throw a wrench into the job but I guess time
will tell.

 

[Niek Otten]

The formula is correct. Do you happen to have a system where the list
separator is a semicolon ( instead of a comma (,)?

 

[Marc Fleury]

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?

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.

 

[JC]

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()""mmdd"")>TEXT(C3,""mmdd"")*1,0),MONTH(C3),DAY(C3))-TODAY()
For what it is worth;
That "star" falls in place between the last ""mmdd"") and the 1,0).
So far it hasn't seemed to throw a wrench into the job but I guess time
will tell.

The correct formula is
=DATE(YEAR(TODAY())+IF(TEXT(TODAY(),"mmdd")>TEXT(C3,"mmdd"),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. "mmdd" not ""mmdd"") 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.

 

[daddylonglegs]

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(),"mmdd")>TEXT(C3,"mmdd")),MONTH(C3),DAY(C3))-TODAY()

 

[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(),"mmdd")>TEXT(C3,"mmdd")),MONTH(C3),DAY(C3))-TODAY()

Hi Daddylonglegs,

That depends on what the convention is for those born on 29th Feb. Personally,
I would celebrate my birthday on 28th Feb in non leap years if I was born on
29th Feb thus keeping it in the same month so I would expect the formula to
return 364 days on 1st March but others may celebrate their birthday on 1st
March in which case 365 would be the answer.

I haven't tried your simplified formula yet but don't see how the
IF(TEXT(TODAY(),"mmdd")>TEXT(C3,"mmdd"),1,0) part can be simplified to
(TEXT(TODAY(),"mmdd")>TEXT(C3,"mmdd")). That doesn't make sense to me.