Birthday stuff

S

SoothSayer

Hi folks.

I have a formula that gives a person's age.

I want to add on below it that says something like:

"There are 42 days until Joe's next birthday..."

Where the apostrophe after BirthName is added.

Or you could say the number of the age ala "XX-tyeth"
type format, so if Joe was 50 (1960 year), it would say:

There are 42 days before Joe (BirthName) becomes 51 years old.

I know it is a simple variant of my formula.

=CONCATENATE(BirthName," is ",(DATEDIF(DOB,TODAY(),"y") & " years, " &
DATEDIF(DOB,TODAY(),"ym") & " months, and "& DATEDIF(DOB,TODAY(),"md") &
" days

Can someone wanting to exercise his or her date coding prowess or
learned skills bang this out for me. I am making one of those workbooks
where folks hate me for making it easy for their friends to track
birthdays and ages.
 
I

isabelle

hi,

i'm not sure if i understand correctly, just tell me.
sorry for the unintended return of line


="There are "&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0"" days""")&
" before Joe ("&TEXT(DOB,"mmm-dd-yyy")&") becomes "&TEXT(1900-YEAR(TODAY()-DOB+DATE(1,0,0)),"0")&" years old"
 
J

joeu2004

I want to add on below it that says something like:
"There are 42 days until Joe's next birthday..."
Click to expand...

This is non-trivial. First, I'm sure you would like it to read "is 1
day" instead of "are 1 days". Second, we have to consider two cases:
(a) when the BD this year is before today; and (b) when the BD this is
on as well as after today. Third, there are issues to consider with
leap years, both the current year and next year in some cases.
Finally, what if the BD is Feb 29?

I suggest that you put =TODAY() into a cell (A1).

First, that allows you to substitute other dates for "today" for
testing purposes; very useful. Second, it is more efficient and more
reliable (near midnight!) to compute TODAY() only once.

I also suggest that you put the BD this year and next year into
cells. The reason will become evident below. For example:

A2: =DATE(YEAR(A1),MONTH(DOB),DAY(DOB))
A3: =DATE(1+YEAR(A1),MONTH(DOB),DAY(DOB))

I assume that DOB and BirthName below are defined names or cell
references.

Then your formula becomes:

=IF(OR(A2-A1=1,A3-A1=1), "There is 1 day before ",
"There are " & IF(A2>=A1,A2,A3)-A1 & " days before ")
& BirthName & "'s birthday"

This will result in "0 days before" when today is the BD. You might
prefer:

=IF(A2=A1, "Today is " & BirthName & "'s birthday",
IF(OR(A2-A1=1,A3-A1=1), "There is 1 day before ",
"There are " & IF(A2>=A1,A2,A3)-A1 & " days before ")
& BirthName & "'s birthday")

Normally A2-A1=1 is sufficient. A3-A1=1 is needed when today is Dec
31.

As you can see A2 and A3 (BD this year and next year) must be
referenced several times. That is one reason why it is best to
compute them only one time. I do not simply add 365 because of the
leap year and Feb 29 BD issues.

Note that those formulas ignore the issue raised if the BD is Feb 29.
As written, they will assume that the anniversary of the BD is on Mar
1 of non-leap years. But many people choose (and some laws might
require them) to use Feb 28.

You need to decide how you want to handle the Feb 29 BD. The
adjustment, if any, would go into A2 and A3. Another reason to put
those formulas into A2 and A3.
Or you could say the number of the age [...],
it would say:
There are 42 days before Joe (BirthName) becomes 51 years old.
Click to expand...

This becomes relatively easy once the BD this year and next year are
computed in cells (e.g. A2 and A3).

=IF(A2=A1, "Today, ",
IF(OR(A2-A1=1,A3-A1=1), "There is 1 day before ",
"There are " & IF(A2>=A1,A2,A3)-A1 & " days before "))
& BirthName & " becomes "
& IF(A2>=A1,YEAR(A2),YEAR(A3))-YEAR(A1) & " years old"

Note that I do not bother to handle the special case of 1 year
old ;-). Seriously, is that an issue?
I know it is a simple variant of my formula.
=CONCATENATE(BirthName," is ",(DATEDIF(DOB,TODAY(),"y")
& " years, " & DATEDIF(DOB,TODAY(),"ym") & " months, and "
&  DATEDIF(DOB,TODAY(),"md") & " days
Click to expand...

That formula is flawed for several reasons. In addition to the issues
discussed above, there is also the issue that it has been reported
that in XL2007 SP2 and later (including any XL2010?),
DATEDIF(...,"md") is unreliable. Google "datedif md" without quotes
for details.
 
S

SoothSayer

Oh, my God. I quit. Is it really that much?

I mean, I knew there were a few technical glitches, and it (Excel) does
how old you are just fine, but couldn't we simply fake the future date of
the birthday, and subtract the current derived age from it to get a
result?


I want to add on below it that says something like:
"There are 42 days until Joe's next birthday..."
Click to expand...

This is non-trivial. First, I'm sure you would like it to read "is 1
day" instead of "are 1 days". Second, we have to consider two cases:
(a) when the BD this year is before today; and (b) when the BD this is
on as well as after today. Third, there are issues to consider with
leap years, both the current year and next year in some cases.
Finally, what if the BD is Feb 29?

I suggest that you put =TODAY() into a cell (A1).

First, that allows you to substitute other dates for "today" for
testing purposes; very useful. Second, it is more efficient and more
reliable (near midnight!) to compute TODAY() only once.

I also suggest that you put the BD this year and next year into
cells. The reason will become evident below. For example:

A2: =DATE(YEAR(A1),MONTH(DOB),DAY(DOB))
A3: =DATE(1+YEAR(A1),MONTH(DOB),DAY(DOB))

I assume that DOB and BirthName below are defined names or cell
references.

Then your formula becomes:

=IF(OR(A2-A1=1,A3-A1=1), "There is 1 day before ",
"There are " & IF(A2>=A1,A2,A3)-A1 & " days before ")
& BirthName & "'s birthday"

This will result in "0 days before" when today is the BD. You might
prefer:

=IF(A2=A1, "Today is " & BirthName & "'s birthday",
IF(OR(A2-A1=1,A3-A1=1), "There is 1 day before ",
"There are " & IF(A2>=A1,A2,A3)-A1 & " days before ")
& BirthName & "'s birthday")

Normally A2-A1=1 is sufficient. A3-A1=1 is needed when today is Dec
31.

As you can see A2 and A3 (BD this year and next year) must be
referenced several times. That is one reason why it is best to
compute them only one time. I do not simply add 365 because of the
leap year and Feb 29 BD issues.

Note that those formulas ignore the issue raised if the BD is Feb 29.
As written, they will assume that the anniversary of the BD is on Mar
1 of non-leap years. But many people choose (and some laws might
require them) to use Feb 28.

You need to decide how you want to handle the Feb 29 BD. The
adjustment, if any, would go into A2 and A3. Another reason to put
those formulas into A2 and A3.
Or you could say the number of the age [...],
it would say:
There are 42 days before Joe (BirthName) becomes 51 years old.
Click to expand...

This becomes relatively easy once the BD this year and next year are
computed in cells (e.g. A2 and A3).

=IF(A2=A1, "Today, ",
IF(OR(A2-A1=1,A3-A1=1), "There is 1 day before ",
"There are " & IF(A2>=A1,A2,A3)-A1 & " days before "))
& BirthName & " becomes "
& IF(A2>=A1,YEAR(A2),YEAR(A3))-YEAR(A1) & " years old"

Note that I do not bother to handle the special case of 1 year
old ;-). Seriously, is that an issue?
I know it is a simple variant of my formula.
=CONCATENATE(BirthName," is ",(DATEDIF(DOB,TODAY(),"y")
& " years, " & DATEDIF(DOB,TODAY(),"ym") & " months, and "
&  DATEDIF(DOB,TODAY(),"md") & " days
Click to expand...

That formula is flawed for several reasons. In addition to the issues
discussed above, there is also the issue that it has been reported
that in XL2007 SP2 and later (including any XL2010?),
DATEDIF(...,"md") is unreliable. Google "datedif md" without quotes
for details.
Click to expand...
 
S

SoothSayer

hi,

i'm not sure if i understand correctly, just tell me.
sorry for the unintended return of line


="There are "&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0"" days""")&
" before Joe ("&TEXT(DOB,"mmm-dd-yyy")&") becomes "&TEXT(1900-YEAR(TODAY()-DOB+DATE(1,0,0)),"0")&" years old"
Click to expand...

Thank you, that looks like the breakdown and I'll try it (I am just
guessing), but I just read the other post, and may be a bit confused in
the next ten minutes or so. I'll let ya know. Now, I am actually going
to try yours... weeeeeee Thanks again.
 
S

SoothSayer

Then my spreadsheet will be a perfect exercise of what the problem with
DATEDIF is.

So, there is still a single formula that would do it?

You also missed the thing where there are problems with suffixes like
nth and th and rst nd and rd (1st 2nd 3rd 4rth 5th 7nth and the like.

Pretty cool stuff though and thanks for the educational and spot on
critique!

So, it (DATEDIF) skips the counting of leap year days?
 
S

SoothSayer

Thank you, that looks like the breakdown and I'll try it (I am just
guessing), but I just read the other post, and may be a bit confused in
the next ten minutes or so. I'll let ya know. Now, I am actually going
to try yours... weeeeeee Thanks again.
Click to expand...

Well, yours worked, but it seems that I may have accuracy issues.
Putting the named range of "BirthName" in, I refined your submission to
this, and thanks for the alternative to "CONCATENATE".

The only problem I see other than those the other poster outlined may
occur (will occur), is that your result is a negative value. I get the
right number of day until so and so is, but the "years old" part is a
negative number. I will be -51 years old!

Maybe it is because today is the end of the world... :) I did not
see where I need to flip the sign of your calculation.

="There are "&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0""
days before """)&BirthName&" ("&TEXT(DOB,"mmmm dd, yyy")&")
becomes"&TEXT(1900-YEAR(TODAY()-DOB+DATE(1,0,0)),"0")&" years old"
 
S

SoothSayer

Well, yours worked, but it seems that I may have accuracy issues.
Putting the named range of "BirthName" in, I refined your submission to
this, and thanks for the alternative to "CONCATENATE".

The only problem I see other than those the other poster outlined may
occur (will occur), is that your result is a negative value. I get the
right number of day until so and so is, but the "years old" part is a
negative number. I will be -51 years old!

Maybe it is because today is the end of the world... :) I did not
see where I need to flip the sign of your calculation.

="There are "&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0""
days before """)&BirthName&" ("&TEXT(DOB,"mmmm dd, yyy")&")
becomes"&TEXT(1900-YEAR(TODAY()-DOB+DATE(1,0,0)),"0")&" years old"
Click to expand...


Actually, it failed with a birthday of 3/25/64. Both results are
negative, and it says that there are negative 57 days before 3/25/2012,
so there is a years worth of days missing from that. Weird.

Got the number of years old he'll be aside from the flipped sign right.

I just saw a couple others, and will try those!

Thanks guys!
 
S

SoothSayer

You could try this:

=CONCATENATE(Birthname," is ", DATEDIF(DOB,TODAY(),"y"),
" years old. There are ",DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY()," days left until ",Birthname,"'s next birthday.")

If you like this approach, you could add logic to deal with the 1 year and 1 day issue.

=CONCATENATE(Birthname," is ", DATEDIF(DOB,TODAY(),"y"),
" year",IF(DATEDIF(DOB,TODAY(),"y")=1,"","s")," old. There ",
IF((DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY())=1,"is 1 day","are " & DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY() & " days")," left until ",Birthname,"'s next birthday.")
Click to expand...


YDM! Thanks.
 
S

SoothSayer

You could try this:

=CONCATENATE(Birthname," is ", DATEDIF(DOB,TODAY(),"y"),
" years old. There are ",DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY()," days left until ",Birthname,"'s next birthday.")

If you like this approach, you could add logic to deal with the 1 year and 1 day issue.

=CONCATENATE(Birthname," is ", DATEDIF(DOB,TODAY(),"y"),
" year",IF(DATEDIF(DOB,TODAY(),"y")=1,"","s")," old. There ",
IF((DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY())=1,"is 1 day","are " & DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY() & " days")," left until ",Birthname,"'s next birthday.")
Click to expand...


The second case works.

Now we need it to say, instead of "next birthday", it should have said
"51st" or "52nd" or "53rd", etc.

I know that requires a lot of parsing code though. Excel should
incorporate tail signatures like that for a number of data designator
realms that various industries use to declare information about stored
data bits (not binary reference).
 
I

isabelle

hi,

="There are "&TEXTE(DATE(ANNEE(AUJOURDHUI());MOIS(DOB);JOUR(DOB))-AUJOURDHUI();"0"" days""")&
" before Joe ("&TEXTE(DOB;"jj-mm-aa")&") becomes "&STXT(TEXTE(1900-ANNEE(AUJOURDHUI()-DOB+DATE(1;0;0));"0");2;NBCAR(
TEXTE(1900-ANNEE(AUJOURDHUI()-DOB+DATE(1;0;0));"0")))&" years old"
 
I

isabelle

sorry,

="There are "&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0"" days""")&" before Joe ("&TEXT(DOB,"jj-mm-aa")&
") becomes "&MID(TEXT(1900-YEAR(TODAY()-DOB+DATE(1,0,0)),"0"),2,LEN(TEXT(1900-YEAR(TODAY()-DOB+DATE(1,0,0)),"0")))&" years old"
 
G

Gord Dibben

The second case works.

Now we need it to say, instead of "next birthday", it should have said
"51st" or "52nd" or "53rd", etc.
Click to expand...

Just as you asked for...................."51st" or "52nd" or "53rd"

=CONCATENATE(Birthname," is ",DATEDIF(DOB,TODAY(),"y"),
" year",IF(DATEDIF(DOB,TODAY(),"y")=1,"","s")," old. There ",
IF((DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY())=1,"is 1 day","are "&DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY()&" days")," left until ",Birthname,"'s "&
ordinalnumber(DATEDIF(DOB,TODAY(),"y")+1)&" birthday.")

You will need this UDF copied to a module in your workbook to make it work.

Function OrdinalNumber(ByVal num As Long) As String
Dim n As Long
Const cSfx = "stndrdthththththth"
n = num Mod 100
If ((Abs(n) >= 10) And (Abs(n) <= 19)) _
Or ((Abs(n) Mod 10) = 0) Then
OrdinalNumber = Format(num) & "th"
Else
OrdinalNumber = Format(num) & Mid(cSfx, _
((Abs(n) Mod 10) * 2) - 1, 2)
End If
End Function


Gord Dibben MS Excel MVP
 
G

Gord Dibben

Without the added UDF......worksheet functions only.

=CONCATENATE(Birthname," is ",DATEDIF(DOB,TODAY(),"y"),
" year",IF(DATEDIF(DOB,TODAY(),"y")=1,"","s")," old. There ",
IF((DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY())=1,"is 1 day","are "&DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY()&" days")," left until ",Birthname,"'s "&
DATEDIF(DOB,TODAY(),"Y")+1&MID("thstndrdth",MIN(9,2*DATEDIF(DOB,TODAY(),"Y")
*(MOD(DATEDIF(DOB,TODAY(),"Y"),100)>2)+1),2)&" birthday.")

Please don't ask me to explain it<g>


Gord
 
S

SoothSayer

Try this:

=CONCATENATE(Birthname," is ", DATEDIF(DOB,TODAY(),"y"),
" year",IF(DATEDIF(DOB,TODAY(),"y")=1,"","s")," old. There ",
IF((DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY())=1,"is 1 day","are " & DATE(YEAR(TODAY())+(TODAY()>=DATE(
YEAR(TODAY()),MONTH(DOB),DAY(DOB))),MONTH(DOB),DAY(
DOB))-TODAY() & " days")," left until ",Birthname,"'s ",DATEDIF(
DOB,TODAY(),"y")+1,MID("thstndrdth",MIN(9,2*RIGHT(DATEDIF(
DOB,TODAY(),"y")+1)*(MOD(DATEDIF(DOB,TODAY(),"y")-10,100)>2)+1),2)," birthday.")

Note:

A UDF may be preferable as it is often easier to follow the logic.
Click to expand...


And I also need to fix the original 'math' as well, no?

My baseline needs proper wording. Dang. I should be able to shoehorn
that one out after today's exercise.

I am pretty sure the group has gone through this before, but this thread
is a pretty good refresher. Thanks, guys -n- gals.
 
S

SoothSayer

Naaah... I just need to now correct my original cell formula, and then
add this one to declare an age, and a span to the 'next event'. :)

I am sure I'll be loved and hated, as usual.

As to explanations, I particularly like the simple parse method of all
the available suffixes... "thstndrdth" << pretty cool stuff.

Thanks... err... your welcome, that is. ;-)
 
I

isabelle

a shorter one

="There are "&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0"" days""")&
" before Joe ("&TEXT(DOB,"jj-mm-aa")&") becomes "&TEXT((1900-YEAR(TODAY()-DOB+DATE(1,0,0)))*-1,"0")&" years old"
 
J

joeu2004

a shorter one
="There are  "
&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),
"0"" days""")&
" before Joe ("&TEXT(DOB,"jj-mm-aa")&") becomes "
&TEXT((1900-YEAR(TODAY()-DOB+DATE(1,0,0)))*-1,"0")
&" years old"
Click to expand...

It does not matter how short you make it, it still returns a negative
number after "There are" for any DOB before TODAY().

PS: Don't forget to change jj-mm-aa to mmm-dd-yyyy as had originally.
 
I

isabelle

thank you joeu2004, this last one returns a positive number, and sorry for "jj-mm-aa"

="There are "&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0"" days""")&
" before Joe ("&TEXT(DOB,"mmm-dd-yyyy")&") becomes "&TEXT((1900-YEAR(TODAY()-DOB+DATE(1,0,0)))*-1,"0")&" years old"
 
J

joeu2004

thank you joeu2004, this last one returns a positive number [....]
="There are  "
&TEXT(DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB))-TODAY(),"0"" days""")
&" before Joe ("&TEXT(DOB,"mmm-dd-yyyy")&") becomes "
&TEXT((1900-YEAR(TODAY()-DOB+DATE(1,0,0)))*-1,"0")&" years old"
Click to expand...

Not on my computer ;-). If TODAY() returns May 21 2011, try typing
May 20 1949 or any earlier day in that year into DOB.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate Age...? 6
Excel formula calculating age 4
Age computation 1
Calculate age with an end date 3
Copy Excel Formula to Access 6
changed DOB to age, but if no DOB need cell to be empty. 4
Happy Birthday Formula Flag Needed 12
date of birth age formula 8

Top