WEEKDAY function does not work in all regions

[gkuenzler]

Hey, has anyone ever noticed that the WEEKDAY function returns #Value if your region in the Control Panel is set to Hungarian or Chinese (or possibly others)? I have written some worksheets that are being used globally and discovered this problem when some users in these locations said the formulas were not working. It's simple to test - open a worksheet with the WEEKDAY function in it, then switch regions in the control panel. You will see the error immediately.

So, my question is, how do I get around this? I want the worksheet to calculate correctly (accorting to US conventions) no matter what region the user has selected. Any ideas?

 

[GlennUK]

Nope, I switch and my dates look different, but the WEEKDAY function still works.

 

[Jonmo1]

Works for me. Although I don't have a choice for Chinese in my regional control panel. But I tested with Hungarian.
The date format changed to yyyy.mm.dd
But the Weekday function still worked.

I'm in XP SP2 XL2003 SP3

I would guess the problem actually lies in some other formula you have that is creating dates.
Possibly they are being created in the wrong format.

 

[Jonmo1]

Now if I do this...

If A1 is a formaul like =TODAY()
And B1 is =WEEKDAY(A1)

That works fine, no matter what regional setting.
Because A1 adjusts it's date format automatically when changing the regional settings.

But if I put the date inside the weekday function like this

=WEEKDAY("4/1/2010",1)
This works because "4/1/2010" is a valid date format in English US.

But, If I then change the regional settings, then I get the #Value! error.
Because "4/1/2010" is not a valid date format in Hungarian.

Hope that helps you clear it up.

 

[gkuenzler]

Ah, yes. I see what's happening. I have a chain of cells using the Weekday function from cell to cell, but one of them refers to a cell that's typed in as MM/DD/YYYY format. Thanks for the help - got me right to the heart of the matter.

 

[Jonmo1]

What I would do for that manually entered date, since you want it to work in ANY regional setting...

Break it up into 3 cells, 1 for year, one for month and 1 for day.
Then use the Date funciton to create the date

=DATE(yearcell,monthcell,daycell)

Now that date will adjust accordingly no matter which region is selected.