Solved: date format problem with macro....

[sunilmulay]

Hi there

I have this bit of code which is run on clicking on a control, which enters the user entered date into a particular cell. The cell (K3) has been formatted to the British date system DD-MM-YR. Yet if the user enters 04-01-08 (for 4th Jan 2008), the macro enters 1st of April into the cell!

Hope you can help...

Thanks
Sunil

[VBA]Sub PostTrackStatus01()
Dim mKey As String
Application.ScreenUpdating = False
Sheets("1TL").Unprotect Password:=pWord
Sheets("1TR").Unprotect Password:=pWord
Sheets("1A").Unprotect Password:=pWord
mKey = InputBox("Enter date up to which you are posting progress in the format: DD-MM-YR", _
"Enter Date")

Worksheets("1TR").Range("K3").Value = mKey

End Sub[/VBA]

[mdmackillop]

DD-MM-YY

[sunilmulay]

Sorry that was my typing error. the cell is acutally in a standard British date format DD-MM-YY. the relevant code i'm using is:
[VBA]Sub PostTrackStatus01()
Dim mKey As String
mKey = InputBox("Enter date up to which you are posting progress in the format: DD-MM-YY", _
"Enter Date")

Worksheets("1TR").Range("K3").Value = mKey

End Sub[/VBA]
..but it still assumes the date is being entered in American format...
Thanks
S

[georgiboy]

You could trick it with

Format(mKey,"MM/DD/YY")

Hope this helps

[sunilmulay]

Not sure where to insert that exactly.. I did the following but it didn't work...
[vba]Sub PostTrackStatus01()
Dim mKey As String

Format (mKey = "MM/DD/YY")
mKey = InputBox("Enter date up to which you are posting progress in the format: DD-MM-YY", _
"Enter Posting Date")

Worksheets("1TR").Range("J3").Value = mKey
Worksheets("1ID").Range("F3").Value = mKey
End Sub[/vba]

[georgiboy]

[vba]Sub PostTrackStatus01()
Dim mKey As String
mKey = InputBox("Enter date up to which you are posting progress in the format: DD-MM-YY", _
"Enter Date")

Worksheets("1TR").Range("K3").Value = Format(mKey,"MM/DD/YY")

End Sub[/vba]

[sunilmulay]

Thanks - that worked!
S