input date

  • Thread starter Thread starter Nick Hodge
  • Start date Start date
N

Nick Hodge

Rufino

Only with VBA code or a function in another cell, there is no way with the
user interface of doing this automatically

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
hi all,

is this possible: i will type 011005 and then excel will automatically
format it as 01/10/05 and will be treated as date? thanks.
 
hi Nick!

do you have a sample code there?
Rufino

Only with VBA code or a function in another cell, there is no way with the
user interface of doing this automatically

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
Click to expand...
 
Rufino

This in the worksheet_change event will work for entry in column A. You may
want to do some more work to account for different entry, e.g. I have
accounted for the fact that Excel strips the '0' off 01012004 making it
1012004, but it doesn't take account if for example someone enters a two
digit year

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ReEnable
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
Application.EnableEvents = False
If Len(Target.Value) = 8 Then
Target.Value = _
Left(Target.Value, 2) & "/" & _
Mid(Target.Value, 3, 2) & "/" & _
Right(Target.Value, 4)
End If
If Len(Target.Value) = 7 Then
Target.Value = _
Left(Target.Value, 1) & "/" & _
Mid(Target.Value, 2, 2) & "/" & _
Right(Target.Value, 4)
End If
Application.EnableEvents = True
Exit Sub
End If
ReEnable:
Application.EnableEvents = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Back
Top