WEEKDAY function does not work in all regions

gkuenzler

New Member
Joined
Apr 1, 2010
Messages
2
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Nope, I switch and my dates look different, but the WEEKDAY function still works.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top