Format of a date cell

  • Thread starter Thread starter Glenn Johansen
  • Start date Start date
G

Glenn Johansen

Can someone tell me how to format a date cell so the user does not have to
enter the "/" marks when entering a date?
I would like the user to be able to use the enter "101003" and excel place
in the cell "10/10/03" Anyone? Thanks in Advance.
 
Hi Glenn,

Microsoft Excel relies on the user including the "/" to properly interpret
the value as a date (which is actually stored as an integer serial value of
the number of days since 1/1/1900.) Unfortunately, I do not believe that
there is a formatting option to allow the user to avoid including some
delimiter, such as the "/" or "-" in the value as it is being entered, in
order for Excel to treat it as a date.

Hope this helps!
 
Ok Tom thanks - can this be done with a text field? Like the "special"
format types phone number or ssn?
Can a custom "special" format be made?
--
Thanks.
Glenn Johansen
Software Architects, Inc.

Tom Moore said:
Hi Glenn,

Microsoft Excel relies on the user including the "/" to properly interpret
the value as a date (which is actually stored as an integer serial value of
the number of days since 1/1/1900.) Unfortunately, I do not believe that
there is a formatting option to allow the user to avoid including some
delimiter, such as the "/" or "-" in the value as it is being entered, in
order for Excel to treat it as a date.

Hope this helps!
--
Tom Moore [MSFT]

This posting is provided "AS IS" with no warranties, and confers no rights.

Glenn Johansen said:
Can someone tell me how to format a date cell so the user does not have to
enter the "/" marks when entering a date?
I would like the user to be able to use the enter "101003" and excel place
in the cell "10/10/03" Anyone? Thanks in Advance.
Click to expand...
Click to expand...
 
This option would allow you to enter text such as 101003 in one cell,
and another cell would display 10/10/03, but it would not be formatted
as a date, but only as text.

You would have to look at the text functions in Help, but basically you
are selecting the first 2 characters from the left (LEFT function??),
the 2 characters starting in 3rd place (MID) and the last 2 from the
right (RIGHT).

Hope this helps - though it might just be a bit confusing.
 
I think this can be done as follows (i.e. I haven't extensively tested this
but it seems to work):

Put this Custom Format on the cell: m/d/yy;"DATE"

Then put this event sub in the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
Dim c As Range

Application.EnableEvents = False
For Each c In Target.Cells
With c
If .NumberFormat = "m/d/yy;""DATE""" And Not IsEmpty(.Value)
Then
.NumberFormat = "General"
s = .Value
If Len(s) = 5 Then s = "0" & s
.Value = DateSerial(Right(s, 2), Left(s, 2), Mid(s, 3, 2))
.NumberFormat = "m/d/yy;""DATE"""
End If
End With
Next
Application.EnableEvents = True
End Sub

If you try this, let me know how it works out.

-Dave
 
Back
Top