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.

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"


--
isabelle

On May 20, 4:01*am, SoothSayer <SaySo...@TheMonastery.org> wrote:
> I want to add on below it that says something like:
> "There are 42 days until Joe's next birthday..."

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.

You wrote:
> Or you could say the number of the age [...],
> it would say:
> There are 42 days before Joe (BirthName) becomes 51 years old.

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?

You wrote:
> 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

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.

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?


On Fri, 20 May 2011 17:11:48 -0700 (PDT), joeu2004 <(E-Mail Removed)>
wrote:

>On May 20, 4:01*am, SoothSayer <SaySo...@TheMonastery.org> wrote:
>> I want to add on below it that says something like:
>> "There are 42 days until Joe's next birthday..."
>
>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.
>
>You wrote:
>> Or you could say the number of the age [...],
>> it would say:
>> There are 42 days before Joe (BirthName) becomes 51 years old.
>
>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?
>
>You wrote:
>> 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
>
>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.

On Fri, 20 May 2011 18:47:47 -0400, isabelle <(E-Mail Removed)> wrote:

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

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.

On Sat, 21 May 2011 09:27:37 -0700, SoothSayer
<(E-Mail Removed)> wrote:

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


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?

On Sat, 21 May 2011 09:30:35 -0700, SoothSayer
<(E-Mail Removed)> wrote:

>On Fri, 20 May 2011 18:47:47 -0400, isabelle <(E-Mail Removed)> wrote:
>
>>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"
>
> 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.

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"

On Sat, 21 May 2011 10:19:14 -0700, SoothSayer
<(E-Mail Removed)> wrote:

>On Sat, 21 May 2011 09:30:35 -0700, SoothSayer
><(E-Mail Removed)> wrote:
>
>>On Fri, 20 May 2011 18:47:47 -0400, isabelle <(E-Mail Removed)> wrote:
>>
>>>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"
>>
>> 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.
>
> 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"


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!

On Sat, 21 May 2011 13:16:03 -0400, Ron Rosenfeld <(E-Mail Removed)> wrote:

>On Fri, 20 May 2011 04:01:53 -0700, SoothSayer <(E-Mail Removed)> wrote:
>
>> 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.
>
>
>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.")


YDM! Thanks.

On Sat, 21 May 2011 13:16:03 -0400, Ron Rosenfeld <(E-Mail Removed)> wrote:

>On Fri, 20 May 2011 04:01:53 -0700, SoothSayer <(E-Mail Removed)> wrote:
>
>> 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.
>
>
>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.")


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).