PDA

View Full Version : Solved: Date Format



blackie42
07-12-2013, 01:40 AM
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
07-12-2013, 01:43 AM
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
07-12-2013, 02:20 AM
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
07-12-2013, 02:40 AM
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
07-12-2013, 02:52 AM
Ah yes - just what I was looking for

Many thanks

Jon