auto format for dates

[normpam]

A long time ago I used to use some kind of "Auto" sub routine to check the contents of a cell and automatically perform some kind of action. I believe this has changed in later versions of Excel. Anyway, I'd like to enter a date such as 060506 and have it automatically turn into a date format, ie: 06/05/06. Can 'custom' formats be used to accomplish this?

If not, what is currently used in Excel to automatically check the cell and perform the format change?

Thanks!

 

[indiantrix]

Formula solution:
060506 is in cell A1...
=DATE("20"&RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))

 

[Joe4]

For an automated solution, you can use an Event Procedure Macro, specifically the Worksheet_Change event which is automatically triggered when a new value is typed into a cell.

Right click on the sheet tab name, select View Code, and paste this code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myDate As String

'   Automatically re-format date entries in column A
    If Not Intersect(Target, Range("A1:A65536")) Is Nothing Then
        Application.EnableEvents = False
        On Error GoTo err_fix
        myDate = Format(Target, "000000")
        Target = DateValue(Left(myDate, 2) & "/" & Mid(myDate, 3, 2) & "/20" & Right(myDate, 2))
        Application.EnableEvents = True
    End If
    
    Exit Sub
    
'   Handle erroneous entry
err_fix:
    If Err.Number = 13 Then
        MsgBox "You have not entered a valid 6 digit date"
        Target.ClearContents
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    Application.EnableEvents = True
    
End Sub

This will automatically do what you want, reformat any 6 digit entry into the date.