Summer puzzle: age as text

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

Don Guillett

you can fill in the rest this gives39Years 3 months 6 days


=DATEDIF(K1,TODAY(),"y")& "Years "&DATEDIF(K1,TODAY(),"ym")& " months
"&DATEDIF(K1,TODAY(),"md")&" days"
 
A

Arvi Laanemets

Hi


I think this is somewhat more compact:
=IF(OR(A1="",B1=""),"",A1 & " is " & _
IF(DATEDIF(B1,TODAY(),"Y")=0,"",DATEDIF(B1,TODAY(),"Y") & " years ") _
& IF(DATEDIF(B1,TODAY(),"YM")=0,"",DATEDIF(B1,TODAY(),"YM") & " months ") _
& IF(DATEDIF(B1,TODAY(),"MD")=0,"",DATEDIF(B1,TODAY(),"MD") & " days") _
& " old today.")


Arvi Laanemets
 
S

Stephen Dunn

Eric van Uden said:
Hello Don,

You are right, of course, but this is what I meant by "a less specific
output could be generated with a short formula, but that's cheating."
What I concocted says things like:

John is 40 years, 7 months and 17 days old today.
John is 33 years old exactly.
John is 32 years, 11 months and 28 days old today.
John is 32 years and 10 months old exactly.
John is 33 years and 1 month old exactly.
Click to expand...


Nope, your's says:

John is 33 years and 1 months old exactly

So, unless you make some further totally unnecessary complications, you are
also "cheating".

Give us a decent reason to believe that:

=A1&" is "&
IF(DATEDIF(B1,NOW(),"Y"),DATEDIF(B1,NOW(),"Y")&" year(s) ","")&
IF(DATEDIF(B1,NOW(),"YM"),DATEDIF(B1,NOW(),"YM")&" month(s) ","")&
IF(DATEDIF(B1,NOW(),"MD"),DATEDIF(B1,NOW(),"MD")&" day(s)","")&
" old today"

is unreadable, or inconsistent, and I'm sure someone will oblige.
 
S

Stephen Dunn

Eric van Uden said:
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.
Click to expand...


If you manually break the lines after commas or operators, the formula will
remain useable directly from a copy/paste. It's also more readable.

=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."))
 
E

Eric van Uden

Hello Stephen,

Thanks for the manual line break tip. I wasn't aware of that, and judging by
many posts on different ng's, this is neither common knowledge, nor common
practice. But it works fine! Even when running your example through a
translator, it functioned. Some extra spaces appeared (after separators),
but without causing any problem. You will see below that I've taken it up.

As for your stern remarks:

This was meant to be a puzzle. As in a crossword or similar amusement. It's
trivial. It's summer time over here and this is what I consider holiday
stuff.
I don't want to waste serious people's time, so they should feel encouraged
to ignore this if he or she isn't into this kind of frolicing.
And I did give pointers to indicate the frivolous nature of my enterprise.

You may object to the fact that I stipulate 'rules', but don't take any of
this serious. Please.
You may also object to my correction of Don Guillett's reply, but no
disrespect was intended and the occasions on which I could tell him "your
answer is off", outside this kind of mock problem, are rare to non-existant.
He's great. All was said in the same playful frame of mind.

As for your remark: "Give us a decent reason to believe that: [the proposed
formula] is unreadable, or inconsistent, and I'm sure someone will oblige."
I couldn't possibly. The formula is fine. That is why I opened my reply
saying: "Hello Don, You are right, of course,".

But enough retort.

You obviously went through the trouble of reproducing my buggy formula in a
worksheet, and that is already too much honor, but just maybe the whole
thing is tempting enough to try my second attempt:

=IF(B1=TODAY(),"Hello "&A1&"!",IF(OR(A1="",B1="",
B1>NOW()),"","Today, "&A1&" is "&IF(DATEDIF(B1,NOW(),
"Y")<=0,"",IF(DATEDIF(B1,NOW(),"Y")=1,DATEDIF(B1,NOW(),
"Y")&" year",DATEDIF(B1,NOW(),"Y")&" years"))&
IF(OR(DATEDIF(B1,NOW(),"Y")=0,AND(DATEDIF(B1,
NOW(),"YM")=0,DATEDIF(B1,TODAY(),"MD")=0)),"",
IF(OR(DATEDIF(B1,NOW(),"YM")=0,DATEDIF(B1,NOW(),
"MD")=0)," and ",", "))&IF(DATEDIF(B1,NOW(),"YM")<=0,"",
IF(DATEDIF(B1,NOW(),"YM")=1,DATEDIF(B1,NOW(),
"YM")&" month",DATEDIF(B1,NOW(),"YM")&" months"))&
IF(DATEDIF(B1,NOW(),"YM")=0,"",IF(OR(DATEDIF(B1,
NOW(),"Y")=0,AND(DATEDIF(B1,TODAY(),"Y")>0,
DATEDIF(B1,NOW(),"MD")>0))," and ",""))&IF(DATEDIF(B1,
NOW(),"MD")<=0,"",IF(DATEDIF(B1,NOW(),"MD")=1,
DATEDIF(B1,NOW(),"MD")&" day",G1&" days"))&
IF(OR(AND(DATEDIF(B1,NOW(),"YM")=0,
DATEDIF(B1,NOW(),"MD")=0),
DATEDIF(B1,NOW(),"MD")=0)," exactly.",".")))

Full of reasonless complications, just trying to produce correct sentences
that tell us how old A1 is.
For art's sake. Or John's. And I cheated. If I use TODAY(), Excel spits the
monster right back at me hissing "the bite's too big!".

Have a sizzling day!
 
E

Eric van Uden

I apologize for my many imperfections.
I left a stray G1 in my formula, to be replaced by DATEDIF(B1,NOW(),"MD"),
resulting in:

=IF(B1=TODAY(),"Hello "&A1&"!",IF(OR(A1="",B1="",
B1>NOW()),"","Today, "&A1&" is "&IF(DATEDIF(B1,NOW(),
"Y")<=0,"",IF(DATEDIF(B1,NOW(),"Y")=1,DATEDIF(B1,NOW(),
"Y")&" year",DATEDIF(B1,NOW(),"Y")&" years"))&
IF(OR(DATEDIF(B1,NOW(),"Y")=0,AND(DATEDIF(B1,
NOW(),"YM")=0,DATEDIF(B1,TODAY(),"MD")=0)),"",
IF(OR(DATEDIF(B1,NOW(),"YM")=0,DATEDIF(B1,NOW(),
"MD")=0)," and ",", "))&IF(DATEDIF(B1,NOW(),"YM")<=0,"",
IF(DATEDIF(B1,NOW(),"YM")=1,DATEDIF(B1,NOW(),
"YM")&" month",DATEDIF(B1,NOW(),"YM")&" months"))&
IF(DATEDIF(B1,NOW(),"YM")=0,"",IF(OR(DATEDIF(B1,
NOW(),"Y")=0,AND(DATEDIF(B1,TODAY(),"Y")>0,
DATEDIF(B1,NOW(),"MD")>0))," and ",""))&IF(DATEDIF(B1,
NOW(),"MD")<=0,"",IF(DATEDIF(B1,NOW(),"MD")=1,
DATEDIF(B1,NOW(),"MD")&" day",DATEDIF(B1,NOW(),"MD")&
" days"))&IF(OR(AND(DATEDIF(B1,NOW(),"YM")=0,
DATEDIF(B1,NOW(),"MD")=0),
DATEDIF(B1,NOW(),"MD")=0)," exactly.",".")))

What a great bunch of characters. Must look awfully impressive to an
outsider... <bg>
 
E

Eric van Uden

Hello puzzle-maniacs,

A very interresting go from Jurgen 'keepITcool' Volkerink
(http://members.chello.nl/keepitcool) through
microsoft.public.nl.office.excel
who always seems to come up with an unexpected angle
(my translation, so any errors will be mine):

=IF(OR(B3>=TODAY(),B3=""),"Hello "&A3,
"Today "&A3&" is "&SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(LOOKUP(DATEDIF(B3,TODAY(),
"y"),{0;1;2},DATEDIF(B3,TODAY(),"y")&
{"X";" years and ";" years and "})&LOOKUP(DATEDIF(B3,
TODAY(),"ym"),{0;1;2},DATEDIF(B3,TODAY(),"ym")&
{"X";" month and ";" months and "})&LOOKUP(DATEDIF(B3,
TODAY(),"md"),{0;1;2},DATEDIF(B3,TODAY(),"md")&
{"X";" day";" days"})&".","0X","")," and ",", ",1),", .","."))

and my final go - english version:

=IF(B1=TODAY(),"Welcome "&A1&"!",IF(OR(A1="",
B1="",B1>NOW()),"","Today "&A1&" is "&
IF(DATEDIF(B1,NOW(),"Y")<=0,"",DATEDIF(B1,
NOW(),"Y")&IF(DATEDIF(B1,NOW(),"Y")>1,
" years"," year"))&IF(OR(DATEDIF(B1,NOW(),"Y")=0,
AND(DATEDIF(B1,NOW(),"YM")=0,DATEDIF(B1,
TODAY(),"MD")=0)),"",IF(OR(DATEDIF(B1,NOW(),
"YM")=0,DATEDIF(B1,NOW(),"MD")=0)," and ",", "))&
IF(DATEDIF(B1,NOW(),"YM")<=0,"",IF(DATEDIF(B1,
NOW(),"YM")=1,DATEDIF(B1,NOW(),"YM")&" month",
DATEDIF(B1,NOW(),"YM")&" months"))&
IF(AND(DATEDIF(B1,NOW(),"Y")=0,DATEDIF(B1,
NOW(),"MD")>0)," and ","")&IF(DATEDIF(B1,NOW(),
"YM")=0,"",IF(AND(DATEDIF(B1,TODAY(),"Y")>0,
DATEDIF(B1,NOW(),"MD")>0)," and ",""))&
IF(DATEDIF(B1,NOW(),"MD")<=0,"",IF(DATEDIF(B1,
NOW(),"MD")=1,DATEDIF(B1,NOW(),"MD")&" day",
DATEDIF(B1,NOW(),"MD")&" days"))&
IF(OR(AND(DATEDIF(B1,NOW(),"YM")=0,
DATEDIF(B1,NOW(),"MD")=0),DATEDIF(B1,
NOW(),"MD")=0)," old, exactly."," old.")))

and Dutch version (does anyone care?):

=ALS(B3=VANDAAG();"Welkom "&A3&"!";
ALS(OF(A3="";B3="";B3>NU());"";"Vandaag is "&A3&
" "&ALS(DATUMVERSCHIL(B3;NU();"Y")<=0;"";
DATUMVERSCHIL(B3;NU();"Y")&" jaar")&
ALS(OF(DATUMVERSCHIL(B3;NU();"Y")=0;
EN(DATUMVERSCHIL(B3;NU();"YM")=0;
DATUMVERSCHIL(B3;VANDAAG();"MD")=0));
"";ALS(OF(DATUMVERSCHIL(B3;NU();"YM")=0;
DATUMVERSCHIL(B3;NU();"MD")=0);" en ";", "))&
ALS(DATUMVERSCHIL(B3;NU();"YM")<=0;"";
ALS(DATUMVERSCHIL(B3;NU();"YM")=1;
DATUMVERSCHIL(B3;NU();"YM")&" maand";
DATUMVERSCHIL(B3;NU();"YM")&" maanden"))&
ALS(EN(DATUMVERSCHIL(B3;NU();"Y")=0;
DATUMVERSCHIL(B3;NU();"MD")>0);" en ";"")&
ALS(DATUMVERSCHIL(B3;NU();"YM")=0;"";
ALS(EN(DATUMVERSCHIL(B3;VANDAAG();
"Y")>0;DATUMVERSCHIL(B3;NU();"MD")>0);
" en ";""))&ALS(DATUMVERSCHIL(B3;NU();"MD")<=0;
"";ALS(DATUMVERSCHIL(B3;NU();"MD")=1;
DATUMVERSCHIL(B3;NU();"MD")&" dag";
DATUMVERSCHIL(B3;NU();"MD")&" dagen"))&
ALS(OF(EN(DATUMVERSCHIL(B3;NU();"YM")=0;
DATUMVERSCHIL(B3;NU();"MD")=0);
DATUMVERSCHIL(B3;NU();"MD")=0);
" oud, precies.";" oud.")))
 
S

Stephen Dunn

Hi Eric,

Sorry about the tone, It wasn't my intention to sound "stern". I often
enjoy a challenge just for the sake of it, so how about this one, based on
Jurgen's:

=IF(OR(B$1>=TODAY(),B$1=""),"-","Today "&A$1&" is "&SUBSTITUTE(
DATEDIF(B$1,TODAY(),"y")&
LOOKUP(DATEDIF(B$1,TODAY(),"y"),{0,1,2},{"x"," year"," years"})&
IF(DATEDIF(B$1,TODAY(),"y")*DATEDIF(B$1,TODAY(),"ym")*
DATEDIF(B$1,TODAY(),"md"),", ",
IF(DATEDIF(B$1,TODAY(),"y")*DATEDIF(B$1,TODAY(),"ym")," and ",""))&
DATEDIF(B$1,TODAY(),"ym")&
LOOKUP(DATEDIF(B$1,TODAY(),"ym"),{0,1,2},{"x"," month"," months"})&
IF((DATEDIF(B$1,TODAY(),"y")+DATEDIF(B$1,TODAY(),"ym"))*
DATEDIF(B$1,TODAY(),"md")," and ","")&DATEDIF(B$1,TODAY(),"md")&
LOOKUP(DATEDIF(B$1,TODAY(),"md"),{0,1,2},{"x"," day"," days"}),
"0x","")&" old.")

Steve D.


Eric van Uden said:
Hello Stephen,

Thanks for the manual line break tip. I wasn't aware of that, and judging by
many posts on different ng's, this is neither common knowledge, nor common
practice. But it works fine! Even when running your example through a
translator, it functioned. Some extra spaces appeared (after separators),
but without causing any problem. You will see below that I've taken it up.

As for your stern remarks:

This was meant to be a puzzle. As in a crossword or similar amusement. It's
trivial. It's summer time over here and this is what I consider holiday
stuff.
I don't want to waste serious people's time, so they should feel encouraged
to ignore this if he or she isn't into this kind of frolicing.
And I did give pointers to indicate the frivolous nature of my enterprise.

You may object to the fact that I stipulate 'rules', but don't take any of
this serious. Please.
You may also object to my correction of Don Guillett's reply, but no
disrespect was intended and the occasions on which I could tell him "your
answer is off", outside this kind of mock problem, are rare to non-existant.
He's great. All was said in the same playful frame of mind.

As for your remark: "Give us a decent reason to believe that: [the proposed
formula] is unreadable, or inconsistent, and I'm sure someone will oblige."
I couldn't possibly. The formula is fine. That is why I opened my reply
saying: "Hello Don, You are right, of course,".

But enough retort.

You obviously went through the trouble of reproducing my buggy formula in a
worksheet, and that is already too much honor, but just maybe the whole
thing is tempting enough to try my second attempt:

=IF(B1=TODAY(),"Hello "&A1&"!",IF(OR(A1="",B1="",
B1>NOW()),"","Today, "&A1&" is "&IF(DATEDIF(B1,NOW(),
"Y")<=0,"",IF(DATEDIF(B1,NOW(),"Y")=1,DATEDIF(B1,NOW(),
"Y")&" year",DATEDIF(B1,NOW(),"Y")&" years"))&
IF(OR(DATEDIF(B1,NOW(),"Y")=0,AND(DATEDIF(B1,
NOW(),"YM")=0,DATEDIF(B1,TODAY(),"MD")=0)),"",
IF(OR(DATEDIF(B1,NOW(),"YM")=0,DATEDIF(B1,NOW(),
"MD")=0)," and ",", "))&IF(DATEDIF(B1,NOW(),"YM")<=0,"",
IF(DATEDIF(B1,NOW(),"YM")=1,DATEDIF(B1,NOW(),
"YM")&" month",DATEDIF(B1,NOW(),"YM")&" months"))&
IF(DATEDIF(B1,NOW(),"YM")=0,"",IF(OR(DATEDIF(B1,
NOW(),"Y")=0,AND(DATEDIF(B1,TODAY(),"Y")>0,
DATEDIF(B1,NOW(),"MD")>0))," and ",""))&IF(DATEDIF(B1,
NOW(),"MD")<=0,"",IF(DATEDIF(B1,NOW(),"MD")=1,
DATEDIF(B1,NOW(),"MD")&" day",G1&" days"))&
IF(OR(AND(DATEDIF(B1,NOW(),"YM")=0,
DATEDIF(B1,NOW(),"MD")=0),
DATEDIF(B1,NOW(),"MD")=0)," exactly.",".")))

Full of reasonless complications, just trying to produce correct sentences
that tell us how old A1 is.
For art's sake. Or John's. And I cheated. If I use TODAY(), Excel spits the
monster right back at me hissing "the bite's too big!".

Have a sizzling day!
Click to expand...
 
A

Arvi Laanemets

Hi again

Anyway I hardly can do anything in this heat, so another try:

=IF(OR(A1="";B1="";B1>=TODAY());"";A1 & " is " _
& IF(Year=0;"";Year & " year" & IF(Year=1;" ";"s ")) & _
IF(Month=0;"";Month & " month" & IF(Month=1;" ";"s ")) & _
IF(Day=0;" old exactly";Day & " day" & IF(Day=1;" ";"s ") & " old today"))

where

Year=DATEDIF(Sheet1!$B$1;TODAY();"Y")
Month=DATEDIF(Sheet1!$B$1;TODAY();"YM")
Day=DATEDIF(Sheet1!$B$1;TODAY();"MD")

are named ranges/formulas. I think it looks better.
 
E

Eric van Uden

Hello Stephen,

Well, I must say you got through Jurgen's rather abstract code a lot beter
than I did!
And you successfully took out the little problems that his suggestion
retained.
Specifically the decisions around the placement of 'and' or ', ' seem to be
completely solved, where Jurgen still got 'Today x is 1 year, 1 day.', next
to 'Today x is 11 months and 29 days.' No problem, but not completely
consistent and not the way we would type it manually.

I have to grant him that his code is very slim. I can add that is an extra
task to produce small formulas in a non-english language.
English is more compact as it is, and often the translated formula-names are
unabbreviated (as opposed to the english ones) or even ill-chosen.
The current widget is a good example:

english translation extra occurs balance

if als 1 4 +4
substitute substitueren 2 1 +1
today vandaag 2 15 +30
datedif datumverschil 6 14 +84

total +119

That is 119 extra characters that return no extra functionality whatsoever
and only confuse the eye:

=ALS(OF(B1>=VANDAAG();B1="");"-";"Today "&A$1&" is "&
SUBSTITUEREN(DATUMVERSCHIL(B1;VANDAAG();"y")&
ZOEKEN(DATUMVERSCHIL(B1;VANDAAG();"y");{0;1;2};
{"x";" year";" years"})&ALS(DATUMVERSCHIL(B1;VANDAAG();
"y")*DATUMVERSCHIL(B1;VANDAAG();"ym")*
DATUMVERSCHIL(B1;VANDAAG();"md");", ";
ALS(DATUMVERSCHIL(B1;VANDAAG();"y")*
DATUMVERSCHIL(B1;VANDAAG();"ym");" and ";""))&
DATUMVERSCHIL(B1;VANDAAG();"ym")&ZOEKEN(
DATUMVERSCHIL(B1;VANDAAG();"ym");{0;1;2};
{"x";" month";" months"})&ALS((DATUMVERSCHIL(B1;
VANDAAG();"y")+DATUMVERSCHIL(B1;VANDAAG();
"ym"))*DATUMVERSCHIL(B1;VANDAAG();"md");
" and ";"")&DATUMVERSCHIL(B1;VANDAAG();"md")&
ZOEKEN(DATUMVERSCHIL(B1;VANDAAG();"md");
{0;1;2};{"x";" day";" days"});"0x";"")&" old.")

Still, with 608 characters used (701 in translation), this beats the crap
out of my attempt: 881 characters (1018 characters in translation).
I ended up with 975 characters by cheating, using NOW ('nu') instead of
TODAY ('vandaag') in order to be able to use Dutch words in the string text.
Excel barred me for using too many characters when I didn't. I guess 1024 is
the bloody limit for Excel...

But I get a peace of cake and a drink for returning 'Welcome x!' when a name
is entered with the current date for birthday....;^)


I really enjoyed your code. Thanks for the contribution.

Have a sunny day!
 
E

Eric van Uden

Hello Arvi!

I like your approach, as it takes a lot of the repetition out of the formula
without introducing reference cells. I had considered named formulas myself
and was hoping someone would come up with a suggestion in that direction.
The rotten part is the detail, however. Getting phrases that look like
regular writing. Deciding on the choice for a comma, or 'the word 'and', and
where spaces should be introduced to avoid double spaces...

But I am certainly going to try to work this into your solution myself. Just
to keep me off the overheated streets. What else is there to do when you
live in a cardboard box under a bridge with nothing but a laptop and a
telephone wire tap to get though the day? ;^)

Thanks for the inspiration!

Have a cool and shady day.
 
N

Norman Harker

Hi Eric!

Re Summer puzzle:

Can you wait 6 months? Only it's Winter down here!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Tuesday 22nd July: Gambia (Liberation Day),
Poland (National Liberation Day), Slovenia (People’s Uprising Day),
Swaziland (Birthday King Sobhuza II)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
E

Eric van Uden

Hello Arvi!

This is what I made of the formula I concocted before, now using your named
formula approach:

=IF(C1=TODAY(),"Welcome "&B1&"!",IF(OR(B1="",
C1="",C1>NOW()),"","Today "&B1&" is "&IF(year<=0,"",
year&IF(year>1," years"," year"))&IF(OR(year=0,
AND(month=0,day=0)),"",IF(OR(month=0,day=0),
" and ",", "))&IF(month<=0,"",IF(month=1,month&" month",
month&" months"))&IF(AND(year=0,day>0)," and ","")&
IF(month=0,"",IF(AND(year>0,day>0)," and ",""))&
IF(day<=0,"",IF(day=1,day&" day",day&" days"))&
IF(OR(AND(month=0,day=0),day=0)," old, exactly."," old.")))

day =DATEDIF(Sheet1!$C2;TODAY();"MD")
month =DATEDIF(Sheet1!$C2;TODAY();"YM")
year =DATUMVERSCHIL(Sheet1!$C2;TODAY();"Y")

I'm quite pleased with the result. Nice that I can use TODAY now and not
NOW like before.And I kind of like the way the words day, year and month
form little patterns when you see the formula laid out like this...

The heat is really settling nicely on my brain. It's nearly cooked on one
side.
I suppose I should start thinking about turning over...

Hope you are enjoying yourself as well!
 
S

Stephen Dunn

LOL!


Eric van Uden said:
Hello Norman,

Funny you should call in, because I was just talking about you this morning
at the reception for King Sobhusa's party down in Swaziland. Did I mention
we are having summer and we all go on holidays while the rest of the world
is hard at work? Anyway, Sobhi (for friends) was wondering why you didn't
show up. But of course it is wintertime down there and the nights were a bit
chilly, so perhaps the weather was too uninviting for your taste... ;^)

I did write earlier in the thread: "Have a summer day! PS: I am aware of the
fact that summer only comes to one hemisphere at a time, but you are welcome
to share mine if you are in the other one right now..."

I know that sentence was ambiguous, but why not share both my summer and my
hemisphere. But be warned: they are only mine as a figure of speech, so
treat them nicely, don't wear them out and give them back when you're done
with them...

Please don't freeze.

PS: (1) On authority of lonelyplanet.com the head of state of Swaziland is
King Mswati III.
(2) Your temperatures according to the Sydney Morning Herald are
between 13 and 21 degrees Celsius. You call that winter. When it is winter
over here and you go out in what you are wearing now, your *s are going to
freeze off. (Funny, that always happens when I try to type ears. Should do
something about that autocorrect list...)

A smile a day...
doesn't make a living for a comedian.

Have a wonderful day.
Click to expand...
 
N

Norman Harker

Hi Stephen!

Re:
"but your Winters usually match our Summers!"

And we pinch the designs of the bridge at Newcastle from your area,
blew it up on the photocopier and stuck between North and South
Sydney. All we need now is Newcastle Brown and this place would be
heaven.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 23rd July: Armenia (Martyr’s Day),
Egypt (Revolution Day), Equatorial Guinea (Bata’s Fiesta), Fiji
(Constitution Day), Indonesia (National Children’s Day), Oman
(National Day), Papua New Guinea (Remembrance Day), Syria (Egyptian
Revolution Day). Observances: Rastafarian (Birthday of Haile
Selassie), Neptunalia (Roman)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Eric!

Loved your reply!

I think it entirely inappropriate that you celebrated Yesterday as in
Swaziland it is a public holiday to celebrate the *late* King
Sobhuza's birthday and hardly a matter for celebration. My omission
Yesterday was to fail to mention that it was National Hot Dog Day in
USA and also Pi Approximation Day (not to be confused with Pi day
which, of course is March 21). At least Sobhuza had only one birthday!
Queen Elizabeth II of England (I of Scotland) has two and these are
variously celebrated in different countries. However, England, of all
the Commonwealth countries, does have a public holiday to celebrate it
but leaves to us forelock tugging ex-colonials. Sorry Your Majesty!

I'm in the process of compiling a list of holidays and observances for
different countries for use with the NETWORKDAYS and WORKDAY
functions. You'd be surprised at how many comments and suggestions I
get; mostly polite <vbg>. I'm using various different sources at
present but will probably have to go to the embassies etc to get it
definitive and confirmed (subject to changes) for a projected period.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 23rd July: Armenia (Martyr’s Day),
Egypt (Revolution Day), Equatorial Guinea (Bata’s Fiesta), Fiji
(Constitution Day), Indonesia (National Children’s Day), Oman
(National Day), Papua New Guinea (Remembrance Day), Syria (Egyptian
Revolution Day). Observances: Rastafarian (Birthday of Haile
Selassie), Neptunalia (Roman)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
E

Eric van Uden

Norman,

You brought tears to my eyes with your reply!

What a great enterprise you are undertaking, registering all these official
occasions. What I was wondering: are you going to put in the birtdates of
Sobhuza's children? As you know he had 100 wives and approximately 200
children. That should fill some calendar dates. You say it is glad Sobhuza
has only one birthday? He didn't even have time to celebrate that one while
he lived. His book was full. So we still throw a little party for him every
year to catch up. And Mswati III, who was enthroned at 18 as the youngest
reigning monarch at the time, is hard on his way to follow Sobhuza's fertile
example. In April 2002, at the age of 33, he was already supporting eight
wives, 15 children, and (of course) 200 siblings...

By the way, National Hot Dog Day and Pi approximation day didn't go by
unnoticed in spite of your omission. You were probably not aware that these
are key events in Swaziland because of their coincidence with Sobhi's
birthday. We had pi*the number of Sobhuza's children hotdogs at his party. I
got to eat the last 0.3185308etc. one and was told to eat half of it the
first hour of the party, then half the rest during the second hour and half
of each consecutive leftover bit again every next hour. You would be amazed
how long parties last in Swaziland. Fools for mathematics and calculus they
are. Especially keen on multiplication. So much is evident.

Have a celebration day. Pick any. You've made mine.
 

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

Expanding Date formula 1
Calculate age with an end date 3
changed DOB to age, but if no DOB need cell to be empty. 4
Update Upon Opening based on DATEIF NOW() 10
Nested IF formula question 3
Help with formula? 1
Calculate difference in dates 11
date of birth age formula 8

Top