auto format for dates

normpam

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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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
59,079
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.
 

Forum statistics

Threads
1,141,686
Messages
5,707,825
Members
421,531
Latest member
Fish0926

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top