E
Eric van Uden
Who will take on this summer puzzle? Just for fun? ;^)
My formula below works, but it's too long for my tatste.
Of course a less specific output could be generated with a short formula,
but that's cheating.
Best would be if there were no other cells involved. Maybe named fomulas?
I haven't tried VBA yet, but it's an option...
==========
This produces a sentence describing someone's age like this:
John is 30 years, 9 months and 13 days old today.
A1 has somebody's name
B1 has his/her birth date
C1 has the formula that describes the person's age in years, months and
days:
=IF(B1=0,"",A1&" is
"&IF(DATEDIF(B1,TODAY(),"y")=0,"",DATEDIF(B1,TODAY(),"y")&IF(AND(DATEDIF(B1,
TODAY(),"ym")=0,DATEDIF(B1,TODAY(),"md")=0)," years
",IF(DATEDIF(B1,TODAY(),"md")=0," years and "," years,
")))&IF(DATEDIF(B1,TODAY(),"ym")=0,"",IF(DATEDIF(B1,TODAY(),"md")=0,DATEDIF(
B1,TODAY(),"ym")&" months ",DATEDIF(B1,TODAY(),"ym")&" months and
"))&IF(DATEDIF(B1,TODAY(),"md")=0,"old today
exactly",DATEDIF(B1,TODAY(),"md")&" days old today."))
Keep in mind thet the e-mailprocess adds unexpected line breaks.
When cutting and pasting, make sure the pasted formula becomes a single line
again.
Spaces may be deleted in this action, so that words in the generated
sentences get stuck together.
Put back the spaces in the formula where they are needed:
" is "
" years "
" years and "
" years, "
" months "
" months and "
"exactly"
" days"
" old."
A little shorter by cheating:
=IF(B1=0,"",A1&" is
"&IF(DATEDIF(B1,NOW(),"y")=0,"",DATEDIF(B1,NOW(),"y")&IF(AND(DATEDIF(B1,NOW(
),"ym")=0,DATEDIF(B1,NOW(),"md")=0)," years ",IF(DATEDIF(B1,NOW(),"md")=0,"
years and "," years,
")))&IF(DATEDIF(B1,NOW(),"ym")=0,"",IF(DATEDIF(B1,NOW(),"md")=0,DATEDIF(B1,N
OW(),"ym")&" months ",DATEDIF(B1,NOW(),"ym")&" months and
"))&IF(DATEDIF(B1,NOW(),"md")=0,"old today exactly",DATEDIF(B1,NOW(),"md")&"
days old today."))
My formula below works, but it's too long for my tatste.
Of course a less specific output could be generated with a short formula,
but that's cheating.
Best would be if there were no other cells involved. Maybe named fomulas?
I haven't tried VBA yet, but it's an option...
==========
This produces a sentence describing someone's age like this:
John is 30 years, 9 months and 13 days old today.
A1 has somebody's name
B1 has his/her birth date
C1 has the formula that describes the person's age in years, months and
days:
=IF(B1=0,"",A1&" is
"&IF(DATEDIF(B1,TODAY(),"y")=0,"",DATEDIF(B1,TODAY(),"y")&IF(AND(DATEDIF(B1,
TODAY(),"ym")=0,DATEDIF(B1,TODAY(),"md")=0)," years
",IF(DATEDIF(B1,TODAY(),"md")=0," years and "," years,
")))&IF(DATEDIF(B1,TODAY(),"ym")=0,"",IF(DATEDIF(B1,TODAY(),"md")=0,DATEDIF(
B1,TODAY(),"ym")&" months ",DATEDIF(B1,TODAY(),"ym")&" months and
"))&IF(DATEDIF(B1,TODAY(),"md")=0,"old today
exactly",DATEDIF(B1,TODAY(),"md")&" days old today."))
Keep in mind thet the e-mailprocess adds unexpected line breaks.
When cutting and pasting, make sure the pasted formula becomes a single line
again.
Spaces may be deleted in this action, so that words in the generated
sentences get stuck together.
Put back the spaces in the formula where they are needed:
" is "
" years "
" years and "
" years, "
" months "
" months and "
"exactly"
" days"
" old."
A little shorter by cheating:
=IF(B1=0,"",A1&" is
"&IF(DATEDIF(B1,NOW(),"y")=0,"",DATEDIF(B1,NOW(),"y")&IF(AND(DATEDIF(B1,NOW(
),"ym")=0,DATEDIF(B1,NOW(),"md")=0)," years ",IF(DATEDIF(B1,NOW(),"md")=0,"
years and "," years,
")))&IF(DATEDIF(B1,NOW(),"ym")=0,"",IF(DATEDIF(B1,NOW(),"md")=0,DATEDIF(B1,N
OW(),"ym")&" months ",DATEDIF(B1,NOW(),"ym")&" months and
"))&IF(DATEDIF(B1,NOW(),"md")=0,"old today exactly",DATEDIF(B1,NOW(),"md")&"
days old today."))