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?

 

[Skybluekid]

If you select the column, right click and choose "Format Cells" then in the Number tab, choose the date format ypu want

 

[sheetspread]

=date(right(a1,2)+2000,left(a1,len(a1)-4),mid(a1,len(a1)-3,2))

 

[Rick Rothstein]

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?

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("[COLOR=#FF0000][B]A[/B][/COLOR]")) Is Nothing Then
    For Each Cell In Intersect(Target, Intersect(Columns("[COLOR=#FF0000][B]A[/B][/COLOR]"), 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.

 

[ALLAROUNDOFFICE]

I entered the formula from sheetspread, but Excel came back and said that I entered too many functions for this argument??? Any other ideas?

 

[Rick Rothstein]

I entered the formula from sheetspread, but Excel came back and said that I entered too many functions for this argument??? Any other ideas?

Did you copy/paste it or type it in? I copy/pasted it and it worked fine for me.

 

[ALLAROUNDOFFICE]

Yes, I copy and pasted it. Just tried again and Excel is coming back with same error...I'm just entering this formula in a cell. Do I need to do something else?

 

[sheetspread]

Yes, I copy and pasted it. Just tried again and Excel is coming back with same error...I'm just entering this formula in a cell. Do I need to do something else?

Your date is in cell A1 and the formula is in a different cell, correct?

 

[ALLAROUNDOFFICE]

The date field starts in A3 and I am trying to insert the formula in A3. Should I replace all of the A1's in the formula with A3's?

 

[sheetspread]

The date field starts in A3 and I am trying to insert the formula in A3. Should I replace all of the A1's in the formula with A3's?

That's another reason why Rick's code is best, it changes the format in place rather than requiring another cell. If you still want just a formula, replace the A1s with A3s but you can't put the formula in A3, it has to be next to it or somewhere else. You can still copy and paste as values back into the original though.