Auto Change date format

[Eric Livesay]

I have Excel 2007 and need to be able to have a cell automatically change the entered date format from yyyymmdd (20080702) to dd-mmm-yy (2-Jul-08). I want the cell to do this whenever someone enters the date in the first format and it will then change it to the second format.

Eric

 

[njimack]

Assuming the cell in question is A1, paste this into the sheet module...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    Dim Yr As String, Mth As String, Day As String

    If Target.Column = 1 And Target.Row = 1 Then
        With Target
            .NumberFormat = "General"
            Yr = Left(.Value, 4)
            Mth = Mid(.Value, 5, 2)
            Day = Right(.Value, 2)
            .Value = Day & "-" & Mth & "-" & Yr
        End With
    End If

Application.EnableEvents = True


End Sub

 

[Eric Livesay]

njimack: I think this will work but i need to add a little bit of info that i should have the first time. There are three cells that need this formating. The cells are labeled "start", "floor", and "grad". How do I augment your formula to reflect this?

 

[njimack]

There might be a more efficient method, but this seems to work...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    Dim Yr As String, Mth As String, Day As String

    If Target.Name = Range("Start").Name Or Target.Name = Range("Floor").Name Or Target.Name = Range("Grad").Name Then
        With Target
            .NumberFormat = "General"
            Yr = Left(.Value, 4)
            Mth = Mid(.Value, 5, 2)
            Day = Right(.Value, 2)
            .Value = Day & "-" & Mth & "-" & Yr
        End With
    End If

Application.EnableEvents = True


End Sub

 

[Eric Livesay]

This doesn't work consitently. Sometimes it will stay in the yyyymmdd (20080702) format and sometimes in changes it to dd/mm/yy (02/07/08) format. Thanks for the assistance anyway.

 

[njimack]

Do you have any other code which is disabling events?