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
"Rick Rothstein (MVP -
VB)" wrote in
message ...
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
"Libby" wrote in message
...
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.
"Libby" wrote:
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, Ive 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?
Ive confirmed that were all using Excel 2003.
Many thanks in advance.
Libby x