#### Summer puzzle: age as text

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

--
Eric van Uden
at the foot of the 'bridge too far'

```
 0
Eric
7/21/2003 12:29:21 PM
excel.misc 78881 articles. 5 followers.

21 Replies
396 Views

Similar Articles

[PageSpeed] 35

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

--
Don Guillett
SalesAid Software
Granite Shoals, TX
donaldb@281.com
"Eric van Uden" <eric no @ spam doornroosje.nl> wrote in message
news:ufm85O4TDHA.2148@TK2MSFTNGP11.phx.gbl...
> 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."))
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
>

```
 0
Don
7/21/2003 12:49:55 PM
```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

"Eric van Uden" <eric no @ spam doornroosje.nl> wrote in message
news:ufm85O4TDHA.2148@TK2MSFTNGP11.phx.gbl...
> 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."))
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
>

```
 0
arvi (37)
7/21/2003 1:08:30 PM
```"Eric van Uden" <eric no @ spam doornroosje.nl> wrote in message
news:ertInl4TDHA.2008@TK2MSFTNGP11.phx.gbl...
> 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.
>

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.

> Not every situation gives a perfect sentence, but a lot of them do.
>
> As I said: it's a puzzle.
>
> And if you are inclined to say I'm just looking for the hard way, you're
> obviously right again!
>
> ;^)
>
> Have the best of days.
>
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
> "Don Guillett" <donaldb@281.com> schreef in bericht
> news:eLf7Va4TDHA.2264@TK2MSFTNGP11.phx.gbl...
> > 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"
> >
> > --
> > Don Guillett
> > SalesAid Software
> > Granite Shoals, TX
> > donaldb@281.com
> > "Eric van Uden" <eric no @ spam doornroosje.nl> wrote in message
> > news:ufm85O4TDHA.2148@TK2MSFTNGP11.phx.gbl...
> > > 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."))
> > >
> > > --
> > > Eric van Uden
> > > at the foot of the 'bridge too far'
> > >
> > >
> >
> >
>
>

```
 0
steve9257 (19)
7/21/2003 1:33:52 PM
```"Eric van Uden" <eric no @ spam doornroosje.nl> wrote in message
news:ufm85O4TDHA.2148@TK2MSFTNGP11.phx.gbl...
> 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.

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

> 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."))
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
>

```
 0
steve9257 (19)
7/21/2003 1:42:03 PM
```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.

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

--
Eric van Uden
at the foot of the 'bridge too far'

```
 0
Eric
7/21/2003 5:38:45 PM
```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>

--
Eric van Uden
at the foot of the 'bridge too far'

```
 0
Eric
7/21/2003 5:57:57 PM
```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.")))

```
 0
7/22/2003 1:43:28 AM
```Hi Eric,

Sorry about the tone, It wasn't my intention to sound "stern".  I often
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" <eric no @ spam doornroosje.nl> wrote in message
news:efYX176TDHA.3024@tk2msftngp13.phx.gbl...
> 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
> 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!
>
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
>

```
 0
steve9257 (19)
7/22/2003 11:33:18 AM
```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.

"Eric van Uden" <eric no @ spam doornroosje.nl> wrote in message
news:ufm85O4TDHA.2148@TK2MSFTNGP11.phx.gbl...
> 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."))
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
>

```
 0
arvi (37)
7/22/2003 11:51:53 AM
```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!

--
Eric van Uden
at the foot of the 'bridge too far'

```
 0
Eric
7/22/2003 2:21:09 PM
```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.

--
Eric van Uden
at the foot of the 'bridge too far'

"Arvi Laanemets" <arvi@tarkon.ee> schreef in bericht
news:%23CblseEUDHA.1688@TK2MSFTNGP11.phx.gbl...
> 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.
>
> "Eric van Uden" <eric no @ spam doornroosje.nl> wrote in message
> news:ufm85O4TDHA.2148@TK2MSFTNGP11.phx.gbl...
> > 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."))
> >
> > --
> > Eric van Uden
> > at the foot of the 'bridge too far'
> >
> >
>
>

```
 0
Eric
7/22/2003 2:43:14 PM
```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)
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

```
 0
njharker (1646)
7/22/2003 4:27:38 PM
```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!

--
Eric van Uden
at the foot of the 'bridge too far'

```
 0
7/23/2003 1:04:39 AM
```Yes, but your Winters usually match our Summers! <g>

Steve D.
North East England.

"Norman Harker" <njharker@optusnet.com.au> wrote in message
news:#xWmu4GUDHA.2196@TK2MSFTNGP12.phx.gbl...
> 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)
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
>
>

```
 0
steve9257 (19)
7/23/2003 6:28:46 AM
```LOL!

"Eric van Uden" <ericvanuden@supermail.nl> wrote in message
news:eegzbeKUDHA.3188@tk2msftngp13.phx.gbl...
> 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...
>
>
> 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.
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
> "Norman Harker" <njharker@optusnet.com.au> schreef in bericht
> news:#xWmu4GUDHA.2196@TK2MSFTNGP12.phx.gbl...
> > 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)
> > njharker@optusnet.com.au
> > Excel and Word Function Lists (Classifications, Syntax and Arguments)
> > available free to good homes.
>
>

```
 0
steve9257 (19)
7/23/2003 6:28:59 AM
```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)
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

```
 0
njharker (1646)
7/23/2003 8:00:12 AM
```Hi Eric!

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)
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Eric van Uden" <ericvanuden@supermail.nl> wrote in message
news:eegzbeKUDHA.3188@tk2msftngp13.phx.gbl...
> 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...
>
>
> 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.
>
> --
> Eric van Uden
> at the foot of the 'bridge too far'
>
> "Norman Harker" <njharker@optusnet.com.au> schreef in bericht
> news:#xWmu4GUDHA.2196@TK2MSFTNGP12.phx.gbl...
> > 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)
> > njharker@optusnet.com.au
> > Excel and Word Function Lists (Classifications, Syntax and
Arguments)
> > available free to good homes.
>
>

```
 0
njharker (1646)
7/23/2003 8:19:00 AM
```Norman,

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.

--
Eric van Uden
at the foot of the 'bridge too far'

"Norman Harker" <njharker@optusnet.com.au> schreef in bericht
news:O1x3UMPUDHA.1740@TK2MSFTNGP12.phx.gbl...
> Hi Eric!
>
>
> 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)
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
> "Eric van Uden" <ericvanuden@supermail.nl> wrote in message
> news:eegzbeKUDHA.3188@tk2msftngp13.phx.gbl...
> > 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...
> >
> >
> > 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.
> >
> > --
> > Eric van Uden
> > at the foot of the 'bridge too far'
> >
> > "Norman Harker" <njharker@optusnet.com.au> schreef in bericht
> > news:#xWmu4GUDHA.2196@TK2MSFTNGP12.phx.gbl...
> > > 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)
> > > njharker@optusnet.com.au
> > > Excel and Word Function Lists (Classifications, Syntax and
> Arguments)
> > > available free to good homes.
> >
> >
>
>

```
 0
Eric
7/23/2003 10:06:29 AM
```And it's "a nun" and not "anon".

"Anon" <abc@def.ghi> schreef in bericht
news:838501c3511c\$979feb00\$7d02280a@phx.gbl...
> Any chance you two individuals could take your personal
> love-fest elsewhere and stop clogging up useful pages with
> and fill up each other's inbox.  (Also, try spelling
> lessons, e.g. it is "birthdates" and not "birtdates.")

```
 0
Eric
7/23/2003 1:38:42 PM
```>National Hot Dog Day in USA
Aren't you sorry you didn't get a hot dog?

--
Don Guillett
SalesAid Software
Granite Shoals, TX
donaldb@281.com
"Norman Harker" <njharker@optusnet.com.au> wrote in message
news:O1x3UMPUDHA.1740@TK2MSFTNGP12.phx.gbl...
> Hi Eric!
>
>
> 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)
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
> "Eric van Uden" <ericvanuden@supermail.nl> wrote in message
> news:eegzbeKUDHA.3188@tk2msftngp13.phx.gbl...
> > 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...
> >
> >
> > 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.
> >
> > --
> > Eric van Uden
> > at the foot of the 'bridge too far'
> >
> > "Norman Harker" <njharker@optusnet.com.au> schreef in bericht
> > news:#xWmu4GUDHA.2196@TK2MSFTNGP12.phx.gbl...
> > > 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)
> > > njharker@optusnet.com.au
> > > Excel and Word Function Lists (Classifications, Syntax and
> Arguments)
> > > available free to good homes.
> >
> >
>
>

```
 0
Don
7/23/2003 5:04:08 PM
```Anon,

I, for one, found the dialog entertaining and the puzzle solutions
informative. A break from the everyday grind doesn't hurt every
once in a while.

John

Anon wrote:

> Any chance you two individuals could take your personal
> love-fest elsewhere and stop clogging up useful pages with
> and fill up each other's inbox.  (Also, try spelling
> lessons, e.g. it is "birthdates" and not "birtdates.")
>
> >-----Original Message-----
> >Norman,
> >
> >
> >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
> >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
> >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.
> >
> >
> >--
> >Eric van Uden
> >at the foot of the 'bridge too far'
> >
> >
> >"Norman Harker" <njharker@optusnet.com.au> schreef in
> bericht
> >news:O1x3UMPUDHA.1740@TK2MSFTNGP12.phx.gbl...
> >> Hi Eric!
> >>
> >>
> >> 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
> >>
> >> 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)
> >> njharker@optusnet.com.au
> >> Excel and Word Function Lists (Classifications, Syntax
> and Arguments)
> >> available free to good homes.
> >> "Eric van Uden" <ericvanuden@supermail.nl> wrote in
> message
> >> news:eegzbeKUDHA.3188@tk2msftngp13.phx.gbl...
> >> > Hello Norman,
> >> >
> >> > Funny you should call in, because I was just talking
> >> 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
> >> ;^)
> >> >
> >> > 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,
> >> 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.
> >> >
> >> > --
> >> > Eric van Uden
> >> > at the foot of the 'bridge too far'
> >> >
> >> > "Norman Harker" <njharker@optusnet.com.au> schreef in
> bericht
> >> > news:#xWmu4GUDHA.2196@TK2MSFTNGP12.phx.gbl...
> >> > > 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)
> >> > > njharker@optusnet.com.au
> >> > > Excel and Word Function Lists (Classifications,
> Syntax and
> >> Arguments)
> >> > > available free to good homes.
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >

```
 0
jwilson (359)
7/23/2003 9:56:34 PM

Similar Artilces:

Increment "text" not number
Hi All I use this to show incremental numbers for record in a subform Private Sub Form_BeforeInsert(Cancel As Integer) Me.PayItemID = Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] = " & Me.PayID)) + 1 End Sub So you get Main record ID = 123, 124, 125, etc Sub form ID = 1, 2, 3, 4 (not indexed) Concencated text box = 123 / 1, 123 / 2, 123 / 3, etc Is it possible to increment Letters (A to Z) instead of number so you would have Sub form ID = A, B, C, D Note there are never more than 15 subform records. I just think it would look better and be ea...

Outlook 2007 and hyperlinks in plain text messages
Hello all, Since I have installed Outlook 2007 URLs in plain text messages are not longer displayed as clickable hyperlinks. In HTML messages they are still working. What can I do that these URLs are also displayed as hyperlinks? Thank you an best regards! Markus ...

text box
I want a column of text boxes to automatically expand to fit the date (names) appearing in each row. The Microsoft assistance instructions tell me to click text box on the format menu and then click alignment. I do not get text box on the format menu when in Excell. Therefore, I cannot get to alignment. Can anyone give me an alternative? Thanks You could try selecting them all, and on the Draw menu on the Drawing toolbar, there is an align option . -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) ...

Outlook 2003 SP 2 Word 2003 SP 2 How do I set Outlook to always reply in HTML or RTF? When replying to, or forwarding, an email that is in plain text format, it always uses plain text. I want to force it to use RTF or HTML. I understand why it works this way. It is assuming that the user is sending plain text, that they *can't* understand anything else. In my case, I know who I am sending to and know otherwise. I know I can change it every time, and I do, but it is getting very old. Thanks, Brad. Outlook doesn't not have such an option. You'll need to either change it m...

Converting text to dates
Hi I have inherited an Access 97 database which has the dates in text format eg '20040425'. Changing the data type in design view deletes all the records. Anyone know how I can convert these dates from text to date without data loss. Thanks GLS You can add a new field (of Date type) to your table and then do an Update query to fill it with the date value from your text field. Here is the SQL for an Update query using the DateValue() function to convert the text to a true date: UPDATE [MyTable] SET [MyTable].NewDateField = DateValue(Left([TextField],4) & "/" &...

Add text to slides in Power Point
Hello: For the first time I am atempting to create a slide show in Power Point. I already have all the slides, placed a label on each one and saved the .pps file. However, when I played the .pps it shows the pictures but not the text. Could you tell me what to do? Do I have to save each text and how? Thanks a lot. Which version of PPT are you using, and how did you add the labels? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover ...

Change a comma separated text file and save it.
Hello All, I need some help on comma separated text files, changing them and saving them. I have got two text files. The first file "ES_EF_AIG_20141440_105708_d.txt" has employee payment record exactly like below: "C0000000519","","","MR STEPHEN AGUTTER","10 COLCOKES ROAD","BANSTEAD","SURREY","","","","","","SM7 2EW","MAL","AGUTTER","NS686159A","0000153363","CDN","E","EMPLOYEE",&quo...

How to validate time format from a text field?
The goal is to normalize data before converting text fields to datetime fields. There are start times and end times stored in text fields of a table. The problem is that any criteria used in a query causes errors when any function causes text to be converted to a datetime type, and the text field does not contain a valid date or time. I'm familiar with some of the validation functions such as isNull(), but I can't find anything like isTime(). How can I test existing data for valid time format? IsDate works on time as well. -- Doug Steele, Microsoft Acce...

repeat text in same exact cells in multi worksheets
I have a workbook and have 20 worksheets in the book. example of what I would like to do. on the worksheet in the same exact numbered cells I want to put the text in once and then put a forumla to make it copy the info to 2-19 pages of the workbook. like 1 of 20 pages, 2 of 20 pages - I only want to put the "of 20 pages" once then have it when it's changed it would automatically change the other work books if I had 21 pages one time then it would change the others. I hope this makes sense. Thank you! One way This needs to be done once only, select the 2nd to the 20th she...

How do I change the text in a workbook from upper to lower case
Hi Please can anyone help with this one! I have a large workbook that I have created (just with text - no formulas) and I have done it in caps but now want to change all the text to lower case without having to re-type it all. Excel hslp is confusing so can anyone tell me how to do it please Thanks Mike Hi see: http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany mike wrote: > Hi > > Please can anyone help with this one! > > I have a large workbook that I have created (just with text - no > formulas) and I have done it in caps but now...

Dynamic Chart title and text formula?
I have a series of charts that I'd like to create a title for using the left function. I'd like the chart titles to be the result of =LEFT(B7,7). I know how to pull the full contents of a cell but thought of saving a step to avoid doing this as a separate cell formula and point the chart title to that. Any ideas? Thanks!!! The formulas in the chart's text elements can only be link formulas. Splurge and use the extra cell, it doesn't cost much. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 4/15/2010 4:40 PM, Cec...

Number stored as text
I have number stored as text. It has a small green triangle at the upper left of the cell. If I click on the cell it has a yellowsign with a ! and if I click on that a menu and "convert to number" This works fine one cell at a time. I need to convert columns the cell format has no effect on the column of cell I need to change. I did un click the protect on the last tab. Suggestions? Sueanne copy an empty cell. select your range of offending cells Edit|paste special|check Add sueanne wrote: > > I have number stored as text. It has a small green triangle at the ...

The message not display the body text
Some body help!!! In Ms Outlook 2003 I have 2 accounts set up, 1 with a POP3 access and 1 aol account using IMAP. Suddenly, both accounts are able to connect and download messages, but the messages don't have any body text. When the message is opened, all the information appears in the top of the message form, but the message body is blank. and alert the message box is "The Message not yet download try download and open again" Did something with my outlook 2003?? Best Regards, Chen ...

Text in a Formula
Hi, I once knew how to add text to a formula, which I'd use to explain the formula rather than using cell comments. I can't for the life of me remember how to do this. Any help would be greatly appreciated. James Hi James Try =Sum(A1:A100)+N("This adds the total Gross Amount") Change to suit. Regards Roger Govier James Hamilton wrote: > Hi, > > I once knew how to add text to a formula, which I'd use to explain the > formula rather than using cell comments. > > I can't for the life of me remember how to do this. > > Any help wou...

format in text boxes
In a sql server report service 2005 report, I need to format a few paragraphs before I displayed a chart. Thus, can you tell me is there a way to format messages in paragraphs foramt like you can in word? If this is not an option, do I need to use special carriage control and paragraph indicator characters like '\C'> Thanks! Hi Can you format by T-SQL? "midnight" <midnight@discussions.microsoft.com> wrote in message news:BCFF67CE-FC98-4E73-B814-2E485D2F0548@microsoft.com... > In a sql server report service 2005 report, I need to format a few...

Age is coming up wrong
This is an unbound in a form I noticed that this part of the formula is not calculating correct. For example [DOB] is 2/27/07 and the answer comes back 8 months. The child is 7 months until Octboer 27th. Does anyone know what I did wrong in this formula. This is the part that is wrong: IIf(DateDiff("m", [DOB],Date())<24,DateDiff("m",[DOB],Date()) & " Months", This is the actual formula: =IIf(DateDiff("d",[DOB],Date())<60,DateDiff("ww",[DOB],Date()) & " Weeks",IIf(DateDiff("m",[DOB],Date())<24,DateDiff(&...

Auto Text
How do I have Excel enter the path of a workbook like the auto text function does in Wrod? Howard Assuming you want the path and name in a cell you must enter a formula in that cell. See Bob Phillips' site for various combinations. http://www.xldynamic.com/source/xld.xlFAQ0002.html OR use a macro assigned to a button or shortcut key. Sub PathInCell() ActiveCell.Value = ActiveWorkbook.FullName End Sub Gord Dibben Excel MVP On Wed, 16 Mar 2005 12:13:06 -0800, Howard <Howard@discussions.microsoft.com> wrote: >How do I have Excel enter the path of a workbook like the au...

Put a variable into text ?
I have 60 lines like this one TextBox01.Text = .. TextBox02.Text = .... .... .... Now I would like to make it more simple by setting a variable - X - into the TextBox-text and then do a loop 60 time instead of writing 60 lines. My id�e was to do it like this - TextBox & X &.Text But it does not work. What is wrong ? -- My Photoalbum www.photo.activewebsite.dk If userform controls Dim ctl As msforms.Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then ctl.Value = X End If Next ctl -- HTH Bob Phi...

code for text to columns
Let's say I want a worksheet to do "text to columns" (delimited) ever time it opens? For example: Text to columns, delimited, by space for column A. Thanks again all...Vat -- Message posted from http://www.ExcelForum.com Hi, Vato, Add this code to your main sheet module1.bas.(In VB editor, menu Insert->Module) Sub auto_open() Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, S...

Extract text strings
To all: I have looked throughout the forum and cannot find the solution to my problem. I have a document that has text strings within single quotes (e.g. 'this is sample text') I need to pull all of these out and place them in another document (or preferrably an excel spreadsheet). If someone could point me to some VBA code, that would be much appreciated. If you have Word 2003 or above, you can use the "Find All" feature to do this; see http://sbarnhill.mvps.org/WordFAQs/SpecialFind.htm#CopyingSelectedText (scroll up from that section to get the ...

Count first occurance of text
I have a row of 24 cells, some are blank and others have text. I am trying to create a formula that returns the first instance of text being used. I have tried using a match, but it gives me an error because I am trying to pull text not a number. thanks Here's one dart throw .. Assume your 24 source cells are A2:A25 Place in say, B2, normal ENTER to confirm: =INDEX(A2:A25,MATCH(TRUE,INDEX(ISTEXT(A2:A25),),0)) Bullseye? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Chad Wods...

Combo box and text box dependency
I have a combo box 'cboRepairType' where the Rowsource is: SELECT * FROM tblGrips ORDER BY GripType 'tblGrips' has three fields GripType -Text BuyPrice -Currency SellPrice -Currency I have a text box 'txtSellPrice' and the following code to accompany it "Private Sub cboRepairType_AfterUpdate() Me.txtSellPrice = cboRepairType.Column(2) End Sub" Why does the value from 'tblGrips.SellPrice' not show in the text box. If I change the .Column(2) to .Column(1) I get the BuyPrice field value. Ideally I would like to use both of these values Any adv...

Converting an INI text file to a dll resource
I have an MFC application which opens a text file and uses its content. I want to use it as a resouce - like a compiled dll that the application will load and use. Is it possible and how? Any reference would be appreciated. Greg Just store it as a binary or string resource, dump it to file. You cannot use it internall without your own INI parser. -- - Mark Randall http://www.temporal-solutions.co.uk http://zetech.swehli.com "Those people that think they know everything are a great annoyance to those of us who do" Isaac Asimov "Greg" <greg@ranger.com> wr...

Link Excel cell data to Powerpoint text boxes
Hi there I am using Excel 2002. On cell A1, I have text "Apple", On cell A2, I have text "Banana". I also have a powerpoint file with two text boxes What i wanna do: 1) To create a link between this Excel file and this Powerpoint files. So, that, "Apple" will appear in one text box in Powerpoint, and "Banana" will appear in another text box. 2) Also, whatever i change or update the Excel cell e.g. "Apple", it will be changed in Powerpoint automatically. Any tips? Thanks a lot. cheers:) Jon Peltier a Excel MVP has a macro that will d...

Prompted to convert the file's text encoding...
When I try to open any excel file from my second computer (Office 2003) it prompts me to convert the file because it can't recognize the text encoding. The file preview is all junk characters and no other encoding makes it readable. I can open these files fine on my laptop, which is also running Office 2003. Any insight would be appreciated! ...