PDA

View Full Version : Solved: date format problem with macro....



sunilmulay
10-24-2008, 07:46 AM
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

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

mdmackillop
10-24-2008, 08:20 AM
DD-MM-YY

sunilmulay
10-24-2008, 07:49 PM
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:
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
..but it still assumes the date is being entered in American format...
Thanks
S

georgiboy
10-24-2008, 10:21 PM
You could trick it with

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

Hope this helps

sunilmulay
10-24-2008, 10:31 PM
Not sure where to insert that exactly.. I did the following but it didn't work...
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

georgiboy
10-24-2008, 10:33 PM
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

sunilmulay
10-24-2008, 11:07 PM
Thanks - that worked!
S