Typing in dates

[ALLAROUNDOFFICE]

How can I hard code a column of cells to convert the user entered data of "042614" or "42614" into a specific date format such as 04/26/2014?

 

[ALLAROUNDOFFICE]

I replaced all the A1's with A'3's and insert the formula in A2 - Still no good. I will try Rick's method. Thanks.

 

[ALLAROUNDOFFICE]

Beautiful Rick! Thank you!

 

[sheetspread]

(Here's how the formula should have looked anyway)

Excel 2010
	A	B
1	42814	4/28/2014
2	042814	4/28/2014
Sheet3

Cell Formulas
Range	Formula
B1	=DATE(RIGHT(A1,2)+2000,LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2))
B2	=DATE(RIGHT(A2,2)+2000,LEFT(A2,LEN(A2)-4),MID(A2,LEN(A2)-3,2))

 

[ALLAROUNDOFFICE]

Thanks - I will try this because the program by Rick is only working in the first cell and then converts wrong. It is turning 042614 into 09/01/16!

 

[ALLAROUNDOFFICE]

That puts the date in Column C instead of Column A????

 

[Rick Rothstein]

Thanks - I will try this because the program by Rick is only working in the first cell and then converts wrong. It is turning 042614 into 09/01/16!

It does not do that for me... I get 4/26/2014 no matter what cell in Column A that I enter it in.

 

[ALLAROUNDOFFICE]

you can do this conversion automatically using the following event code. Assuming your column is column a...

private sub worksheet_change(byval target as range)
  dim cell as range
  on error goto nothingincolumn
  if not intersect(target, columns("a")) is nothing then
    for each cell in intersect(target, intersect(columns("a"), activesheet.usedrange))
      if not isdate(cell.value) and not cell.value like "*[!0-9]*" then
        cell.value = format(cell.value, "0/00/00")
      end if
    next
  end if
nothingincolumn:
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. Note... If you are using xl2007 or above, make sure you save your file as an "excel macro-enabled workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "ok" (depending on the button label for your version of excel) the next time you open your workbook.

this is only working in the first cell in column a. The next cell down in converting 42614 into 9/1/2016 - any help would be appreciated!

 

[ALLAROUNDOFFICE]

Do I need to format that column in a certain way before enabling the event code procedure possibly?

 

[ALLAROUNDOFFICE]

4/26/2014

9/1/2016

<tbody>
</tbody>

 

[Rick Rothstein]

Do I need to format that column in a certain way before enabling the event code procedure possibly?

I think I see the problem... the first time you put a value in a particular cell, the cell's format changes from General to Date... if you change that cell, the Date format screws things up. Here is my code modified to fix this problem (the line of code I added to fix the problem is shown in red)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  On Error GoTo NothingInColumn
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    For Each Cell In Intersect(Target, Intersect(Columns("A"), ActiveSheet.UsedRange))
      If Not IsDate(Cell.Value) And Not Cell.Value Like "*[!0-9]*" Then
         [COLOR=#FF0000][B]Cell.NumberFormat = "General"[/B][/COLOR] 
        Cell.Value = Format(Cell.Value, "0/00/00")
      End If
    Next
  End If
NothingInColumn:
End Sub