Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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 "Libby" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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),"ddd d"),"NA") -- Biff Microsoft Excel MVP "Libby" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
Hi Rick,
I tested it on my German Excel. It works. Nice one. Regards, Bernd |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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 "Bernd P" wrote in message ... Hi Rick, I tested it on my German Excel. It works. Nice one. Regards, Bernd |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
Bernd, does this work in your German version of Excel?
B4 = March (text entry, not a formatted date) B5 = 2008 (numeric entry, not a formatted date) =--(B4&"/"&B5) In my U.S. English version it returns 39508 (serial date for March 1 2008) I must be missing something from the orginal post: 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. I interpret that to mean B4 is the TEXT name of the month but apparently my interpretation is incorrect if Rick's formula does what the OP wants. =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") If my interpretation was correct then the DATE functions would crash. -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hi Rick, I tested it on my German Excel. It works. Nice one. Regards, Bernd |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
"Rick Rothstein \(MVP - VB\)"
wrote... 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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),TEX T(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. 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula only works on some computers
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)" 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),TE XT(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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula works but formula shows in cell??? | Excel Worksheet Functions | |||
Macro works differently on different computers, same version of Ex | Excel Discussion (Misc queries) | |||
IF formula works one way and not the other | Excel Worksheet Functions | |||
How do I convert works file to excel without works software? | Excel Discussion (Misc queries) | |||
Formula Works Only Sometimes??? | Excel Worksheet Functions |