datevalue and languages

[NicolaasH]

Hi,
Situation:
I have build an elaborate spreadsheet which will be shared by several people. In this one can select a month (written out: January, February etc.)and a year from two validation lists. I have then used the DATEVALUE function to convert these parameters from the two cells into a date.
Challenge:
This worked fine on my computer which has English regional options and language selected. However on a Dutch configured computer the function returned a #VALUE! error, since it does not recognize the English month names.
Question:
1. Is there a way of changing the settings in the Excel sheet which might solve this (without going into the Control Panel or using VLookup functions and the like)?
2. Would it be possible to change these settings automatically using VBA code?

Thanks!
P.S. I have also posted this message in the VBA forum if it is more appropriate there.

 

[SkipVought]

Hi,

If you are using a List on a sheet...
[tt]
DateSerial(SelectedYear,match(SelectedMonth,MonthList,0),1)
[/tt]


Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[SkipVought]

Sorry, I usede the incorrect function...
[tt]
=DATE(SelectedYear,match(SelectedMonth,MonthList,0),1)
[/tt]



Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[NicolaasH]

That would work, but it is like using Vlookup, I would rather just set the whole sheet to English if possible.

 

[SkipVought]

You have chosen to use English Literals.

If you were to use DATES and from each DATE format the YEAR, using the appropriate Regional Settings, the FUNCTIONS would return the values you are looking for.

Otherwise, buy some software to translate from one language to another.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}