This will change the dates as you enter them and allow them to be used in other cell formule as dates! May be a bit complex if your not sure of VBA, if so someone/I can expand
set the format of the col to be used to TEXT first!
An event handler is a small bit of macro code that gets executed every time that a certain event
happens. In this case, we want the macro to run whenever you change a cell. To set up an event handler, follow these steps:
An event handler is associated with just a single worksheet. Start from that worksheet and open the VB editor.
In the upper left window (Project - VBA Project) double click the name of your worksheet.
Enter the following lines of code:
Private Sub Worksheet_Change(ByVal Target As Range)
ThisColumn = Target.Column
ThisRow = Target.Row
If ThisColumn = 1 And ThisRow > 1 Then ' avoids row 1 (headers!) and assumes date is in col 1
UserInput = Target.Value
If UserInput > 1 Then ' ie a long number is entered
NewInput = Format(Left(UserInput, 2) & "/" & Mid(UserInput, 3, 2) & "/" & Right(UserInput, 2), "short date")
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub
Any time a cell is changed in col 1,row 2 onwards the cell that was changed is passed to this program in the variable called "Target". When someone enters a date as 012204, it will evaluate to a number less than one. The If block makes sure to only change cells if they are greater than one. I use the left() and right() mid() functions to break the user input into month year day and insert a "/" in between.
Whenever the user enters "012204", the program will change this entry to "short date". check out formating if you want a different style and change this!, As you are in the US I hope this produces the right result if not post back.
If you want to limit the program to only work on certain cols and rows , you can check the value of Target.Column etc and only execute the code block if you are in the first "n" columns: again if usure post back...
If you ever want to make changes to the worksheet areas being effected (for example is you need to add formulas or change headings, etc.) you can turn off the event handler with this short macro:
Sub TurnEventHanderOff()
Application.EnableEvents = False
End Sub
You can turn event handlers back on with this macro:
Sub TurnEventHanderOff()
Application.EnableEvents = True
End Sub
If you take this concept and change it, there is an important concept to be aware of. When the event handler macro assigns a new value to the cell referenced by Target, Excel counts this as a worksheet change. If you do not briefly turn of event handlers, then Excel will recursively start calling the event handler and you get unexpected results. Before making a change to a worksheet in a change event
handler, be sure to temporarily suspend event handling with the Application.EnableEvents line.
GS (adapted from other ideas)