Formula only works on some computers

[Libby]

Hi Guys,

I have a spreadsheet with B9:B39 containing the numbers 1 to 31, B4 = a
month and B5 = a Year.
The month and year cells are validated so the user chooses "April" etc from
a drop down list and likewise "2008" from a dropdown list in the year cell.

In a column to the left of the numbers, I’ve put this formula which displays
the day of the week for the number for the selected month/year.
=IF(ISERROR(DATEVALUE(B9&"/"&$B$4&"/"&$B$5)),"n/a",TEXT(B9&"/"&$B$4&"/"&$B$5,"dddd"))
If there is no day for that year e.g. 31st Feb, then n/a is displayed.

The same formula without the error trapping is this.
=TEXT(B9&"/"&$B$4&"/"&$B$5,"dddd")

This works fine for me and for everyone in the UK. However, people in
Germany have been experiencing one of two problems.
To see the errors I sent them a version without the error trapping.

1) Instead of displaying the day of the week, “dddd†is displayed.
2) #value! is displayed

When they send these spreadsheets back to me I can see these errors, but as
soon as I change the month, order is restored.

Does anyone know why this is happening (in Germany) and how to rectify it?
I’ve confirmed that we’re all using Excel 2003.

Many thanks in advance.

Libby x

 

[Rick Rothstein \(MVP - VB\)]

You may be running into the problem that everyone does not format dates the
way you do. It looks like your short date formatting is dd/mm/yyyy whereas
on my system it is mm/dd/yyyy. Anyway, this formula should work on any
system...

=IF(DATE($B$5,$B$4,$B9)<=DATE($B$5,$B$4+1,0),TEXT(WEEKDAY(DATE($B$5,$B$4,$B9)),"dddd"),"n/a")

Rick

 

[T. Valko]

The problem is more than likely due to regional date settings and the way
DATEVALUE evaluates its argument under those regional settings.

Assume you have the month names listed in the range of cells J1:J12. (this
can also be the source for the month name drop down list)

Enter this formula in A9 and copy down to A39:

=IF(TEXT(DATE(B$5,MATCH(B$4,J$1:J$12,0),B9),"mmmm")=B$4,TEXT(DATE(B$5,MATCH(B$4,J$1:J$12,0),B9),"dddd"),"NA")

 

[Libby]

Many thanks.
These both work, although the formatting is still and issue as the dddd has
to be tttt in Germany. I've used Rick's as my validation data isn't contained
in a range.

 

[Rick Rothstein \(MVP - VB\)]

Try this variation on my formula and see if it works (I **think** the "aaaa"
returns the day name with the localized spelling for the computer it is
being run on)...

=IF(DATE($B$5,$B$4,$B9)<=DATE($B$5,$B$4+1,0),TEXT(WEEKDAY(DATE($B$5,$B$4,$B9)),"aaaa"),"n/a")

Rick

 

[Rick Rothstein \(MVP - VB\)]

By the way, I just noticed (mainly from looking at Biff's posting) that I
have an extra function call that, while not harmful, is completely
unnecessary... the WEEKDAY function call can be removed and the formula will
still return the correct value.

=IF(DATE($B$5,$B$4,$B9)<=DATE($B$5,$B$4+1,0),TEXT(DATE($B$5,$B$4,$B9),"aaaa"),"n/a")

Rick

 

[Bernd P]

Hi Rick,

I tested it on my German Excel. It works.

Nice one.

Regards,
Bernd

 

[Rick Rothstein \(MVP - VB\)]

Thanks for the confirmation (I couldn't conveniently test it out here). By
the way, aaa will print out the 3-letter abbreviation of the day name (well,
3 letters here in the US, not sure if the length of the abbreviation changes
with the language or not as I have virtually no experience with
international programming issues).

Out of curiosity, given your "It works" comment, is this not an already
well-known format pattern for the TEXT function? I came across it awhile
ago, I don't remember where, in my reading up on Excel (to prepare myself
for volunteering in the Excel newsgroups) and figured it was an already
known thing by the Excel community. In thinking about it, we should probably
always be using aaa or aaaa instead of ddd or dddd as there seems to be no
downside to doing so.

Rick

 

[Harlan Grove]

Try this variation on my formula and see if it works (I **think** the "aaaa"
returns the day name with the localized spelling for the computer it is
being run on)...

=IF(DATE($B$5,$B$4,$B9)<=DATE($B$5,$B$4+1,0),TEXT(WEEKDAY(DATE($B$5,$B$4,$B9)),"aaaa"),"n/a")

....

May work for English and German (and other) regional settings, but I
wonder whether it'd fail for French and presumably other romance
language regional setting where 'a' stands for year.

Internationalization is a PITA.

 

[Rick Rothstein \(MVP - VB\)]

Try this variation on my formula and see if it works (I **think** the

...

May work for English and German (and other) regional settings, but I
wonder whether it'd fail for French and presumably other romance
language regional setting where 'a' stands for year.

Internationalization is a PITA.

That is why I'm glad that in my entire programming career, I have never had
to deal with Internalization issues... I have seen some posting regarding
various treatments required to account for it in compiled VB and it looked
like a horrible thing to have to try and deal with properly. As for the 'a'
for year issue, I guess we'll have to wait for someone with French Excel to
try it out.

By the way, for those still reading this thread... the "aaa" and "aaaa" also
works in the formatting patterns for Custom Formatting cells (with the
question about French Excel still pending).

Rick

 

[Rick Rothstein \(MVP - VB\)]

I don't remember where, in my reading up on Excel (to prepare myself for

volunteering in the Excel newsgroups) and figured it was an already known
thing by the Excel community.

I figured out where I saw the 'aaaa' option (no mention of 'aaa' though, so
I guess I invented that one<g>)... I saw it in one of the several VBA help
files for the Format function. The easiest way to get to the help page is to
type Format in the Immediate window and press F1, then click the See Also
link at the top of the help page that comes up and select "User-Defined
Date/Time Formats (Format Function)" from the list. The 'aaaa' option is
described about a third of the way down the page. BUT get this... the 'aaaa'
option does NOT work in VBA's Format function! Nor does the described 'oooo'
pattern (supposed localized version of the month name) work in VBA's Format
function either! I did (re)discover the 'c' pattern (it does work inside the
Format function) which looks like it could be useful in certain
circumstances. So, I guess I half-remembered the 'aaaa' pattern from having
seen it in the VBA side and just assumed it would work in TEXT, which it
did. By the way, the 'oooo' and 'c' patterns do NOT work inside the TEXT
function. You just got to *love* Microsoft's help file, eh?

Rick

 

[Bob Phillips]

Even if it does work in French, it is not a panacea as although we have aaa
for day and oooo for month, we don't seem to have anything for year. Why
not, no idea, but that is MS for you.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Actually, we do not appear to have oooo for month, at least I couldn't get
it to work. Hmm, but if we did, not having one for year didn't occur to me.
Maybe Microsoft started to try providing an method for internationalizing,
started with aaaa and then gave up when they realized they had forgotten
about French Excel's use of 'a' for year (and then simply forgot to clean up
the help files).<g>

Rick

 

[Bob Phillips]

You're right, it doesn't work here either.

Actually it is even worse. If you want a format such as Mon 12 May, you have
to use =TEXT(A1,"aaa dd mmmm"). Now how good is that? Nice idea, but a
totally useless implementation IMO, in true MS fashion.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)