auto format for dates

normpam

Active Member
Joined
Oct 30, 2002
Messages
256
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!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
Formula solution:
060506 is in cell A1...
=DATE("20"&RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,839
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,332
Members
410,603
Latest member
rseckler
Top