I have alot of dates to enter and only want to enter
numbers without having to use the (/) in between but
can't seem to do it. I'm sure it is something very
simple. I want the format to be mm/dd/yy.
Anyone know how?
Thanks.
You need to convert the dates after you have entered them.
You can do this with a formula, with a regular VBA macro, with an
event-triggered VBA macro, or use the Data/Text to Columns wizard.
With ANY of these methods, if you enter a date in your format, into a cell that
is already displaying a date (i.e. formatted as a date), you will get an
unexpected result. Using the formula option helps as the date conversion is
done in a different cell.
VBA solution (after entry select the range that needs to be converted, then run
the macro):
=================
Sub DateEntry()
Dim c As Range
Dim Yr As Integer, Mn As Integer, Dy As Integer
For Each c In Selection
If Not IsDate(c) And c >= 10100 And c <= 123199 Then
Yr = c Mod 100 + 1900 - 100 * ((c Mod 100) < 30)
Mn = Int(c / 10 ^ 4)
Dy = Int(c / 100) Mod 100
c.Value = DateSerial(Yr, Mn, Dy)
End If
Next c
End Sub
=================
Event macro -- as written converts valid entries in your format if they are
entered in A1:A100. This can be changed.
=================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, AOI As Range
Dim Yr As Integer, Mn As Integer, Dy As Integer
Set AOI = [A1:A100]
For Each c In AOI
If Not IsDate(c) And c >= 10100 And c <= 123199 Then
Yr = c Mod 100 + 1900 - 100 * ((c Mod 100) < 30)
Mn = Int(c / 10 ^ 4)
Dy = Int(c / 100) Mod 100
c.Value = DateSerial(Yr, Mn, Dy)
End If
Next c
End Sub
======================
Formula (doesn't check for valid days, months):
=DATE(MOD(A1,100)+1900+100*(MOD(A1,100)<30),INT(A1/10^4),MOD(INT(A1/100),100))
=====================
Data/Text to columns:
At Step 3 select Date: MDY for the column format
--ron
