How to override windows settings for date?

[Arun]

Hi guys,
i had written a macro to get a date value and convert it to the date format
which i need using the below code.
date = Format(tempworksheet.Cells(1, 1).Value, "yyyy-mmm-dd").

it is getting converted fine when the regional language is US(English).When
i change the language to czech it is not getting converted fine.The month is
taken as date and date as month i.e if i enter 06/05/2007 when in US gives
05-JUN-2007 but when in czech gives 06-MAY-2007 also i am not getting the MAY
in english it is coming in czech. I would like to know if there is some code
to override windows settings.Please help me by repling fast.

Regards,
Arun

 

[Joel]

Check Tools menu - Options. There may be a option to solve your problem.
Look under International. the US version I have may not have the same
options in your version.

 

[Arun]

thanks Joel but as you had stated i dont have an option is there any other
way to do this

 

[ilia]

Maybe something along these lines?

Public Sub mySub()
Dim tempDate As Date
Dim myDate As Date

If (Application.International(xlDateOrder) = 0) Then
' month comes first
myDate = Format(tempworksheet.Cells(1, 1).Value, "yyyy-mmm-
dd")
ElseIf (Application.International(xlDateOrder) = 1) Then
' day comes first
' switch month and day places
tempDate = DateSerial(Year(tempworksheet.Cells(1, 1).Value),
Month(tempworksheet.Cells(1, 1).Value), _
Day(tempworksheet.Cells(1, 1).Value))
myDate = DateSerial(Year(tempDate), Day(tempDate),
Month(tempDate))
ElseIf (Application.International(xlDateOrder) = 2) Then
' year comes first
End If

Debug.Print Format(myDate, "yyyy-mmm-dd")
End Sub

 

[Ilia]

Maybe something along these lines?

Public Sub mySub()
Dim tempDate As Date
Dim myDate As Date

If (Application.International(xlDateOrder) = 0) Then
' month comes first
myDate = Format(tempworksheet.Cells(1, 1).Value, _
"yyyy-mmm-dd")
ElseIf (Application.International(xlDateOrder) = 1) Then
' day comes first
' switch month and day places
tempDate = DateSerial(Year(tempworksheet.Cells(1, 1).Value), _
Month(tempworksheet.Cells(1, 1).Value), _
Day(tempworksheet.Cells(1, 1).Value))
myDate = DateSerial(Year(tempDate), _
Day(tempDate), _
Month(tempDate))
ElseIf (Application.International(xlDateOrder) = 2) Then
' year comes first
' respond appropriately
End If

Debug.Print Format(myDate, "yyyy-mmm-dd")
End Sub