Need Help with Date Format
 

How do I type 042707 and have it display on my Excel colum as 04/27/07?

Every time I type it (without the dashes) it changes on my worksheet. I've
tried every option under Format Cells - Number - Date and have even played
around with the Custom option, but still not getting my correct date to show
up with the dashes.

Need Help with Date Format
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not IsDate(.Value) Then
.Value = DateSerial(.Value Mod 100, .Value \ 10000, (.Value
Mod 10000 - .Value Mod 100) \ 100)
.NumberFormat = "m/d/yy"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bjnno1" wrote in message
...
How do I type 042707 and have it display on my Excel colum as 04/27/07?

Every time I type it (without the dashes) it changes on my worksheet.
I've
tried every option under Format Cells - Number - Date and have even
played
around with the Custom option, but still not getting my correct date to
show
up with the dashes.