Create a cell where a string of numbers are entered, and it formats to a date.

[chadwfreeman]

Hello!

Is there a way to to set a cell to format a string of numbers like 03012009 into the date 03/01/2009 as you type it in? I do a lot of data entry, and this would help to speed up filling out our excel sheets.

Thanks!

 

[Gary Drumm]

Create two empty columns to the right of the date / string column
Format the date / string column as "General"
Go to Data, and click on Text To Columns
Select Fixed width, then click on Next
Click between the 3 and 01, like this 03|012009
Click between the 01 and 2009, like this 03|01|2009
Click on Next
Click on Finish

 

[Rick Rothstein]

Is there a way to to set a cell to format a string of numbers like 03012009 into the date 03/01/2009 as you type it in? I do a lot of data entry, and this would help to speed up filling out our excel sheets.

You can do this with event code (just change the red highlighted text to the range of cells you want to have this functionality)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  For Each Cell In Intersect(Target, Columns("B"))
    Cell.Value = CDate(Format(Cell.Value, "0/00/0000"))
  Next
  Application.EnableEvents = True
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.