Date changes

[Melody Rich]

I have the cell formatted for date 3/14/12. When I enter a date "01/15/22", the displayed date is "07/18/31". How can I get this cell to display the date that I enter without adding "/" between numbers?
Thank you

 

[Joe4]

Welcome to the Board!

It is important to understand how Excel stores dates. It actually stores them as numbers, specifically the number of days since 1/0/1900.
So dates are really just numbers with special date formats.
The value 11522 translates to date 7/18/31, which is why you are seeing that.

If you want it to convert it over to a date according to your specifications, you can use this VBA code, which will run automatically upon manual cell entry.
Right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim d As String

    If Target.CountLarge > 1 Then Exit Sub

'   Fix entry if in column 1
    If Target.Column = 1 Then
        Application.EnableEvents = False
        d = Format(Target, "000000")
        Target = DateSerial(Right(d, 2), Left(d, 2), Mid(d, 3, 2))
        Application.EnableEvents = True
    End If
    
End Sub

I wrote this code to fix any entry made into column A as it happens, but we can change the range to whatever range you want to apply it to.

 

[Melody Rich]

Welcome to the Board!

It is important to understand how Excel stores dates. It actually stores them as numbers, specifically the number of days since 1/0/1900.
So dates are really just numbers with special date formats.
The value 11522 translates to date 7/18/31, which is why you are seeing that.

If you want it to convert it over to a date according to your specifications, you can use this VBA code, which will run automatically upon manual cell entry.
Right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim d As String

    If Target.CountLarge > 1 Then Exit Sub

'   Fix entry if in column 1
    If Target.Column = 1 Then
        Application.EnableEvents = False
        d = Format(Target, "000000")
        Target = DateSerial(Right(d, 2), Left(d, 2), Mid(d, 3, 2))
        Application.EnableEvents = True
    End If
   
End Sub

I wrote this code to fix any entry made into column A as it happens, but we can change the range to whatever range you want to apply it to.

Thank you

 

[Joe4]

You are welcome.

Did that work out for you?
Do you need help making changes to the range?