Solved: Date Format

[blackie42]

Hi,

Is there a way to automatically change the date from e.g. 120558 (as its typed in) to 12/05/1958 (maybe when pressing enter)?

thanks
Jon

[joms]

formatting the cell to a desired date format does not help?

check out this link:
http://office.microsoft.com/en-sg/excel-help/format-a-date-the-way-you-want-HA102809474.aspx

[blackie42]

Nope - tried it.

Format cell to e.g.*14/03/2001 and then enter 120558 = 27/01/2230

Was hoping it could be done in a worksheet change module? But don't know how to code it.

thanks
Jon

[snb]

E.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Len(Target) = 6 Then
Application.EnableEvents = False
Target = Format(Format(Target, "00\/00\/00"), "yyyy-mm-dd")
Application.EnableEvents = True
End If
End Sub

[blackie42]

Ah yes - just what I was looking for

Many thanks

Jon