Auto converting MMDDYY to YYYYMMDD

[D_rhodes]

Hi, I am trying to get a cell to auto format the way that I want it to. The load sheet that I am creating is used by people that are in the habit of entering dates in the MMDDYY format, but I need it to be in a YYYYMMDD format in order to load properly. Is there a way to set it up so that if, for example, a user was to enter "010114" the cell would convert it to "20140101"? I tried using the custom date format, but now when I enter 010114, I get 19270909... I am either going crazy, or excel is trying to mess with me... Help? (note: I need it without the hyphens. i.e. NOT 2014-01-01, but rather 20140101)

 

[Rick Rothstein]

The load sheet that I am creating is used by people that are in the habit of entering dates in the MMDDYY format, but I need it to be in a YYYYMMDD format in order to load properly.

How would a formula be able to tell that the user entered the date using MMDDYY instead of simply using YYMMDD (the correct format, but with a 2-digit year instead of a 4-digit year)?

 

[D_rhodes]

I don't need it to tell whether it was entered as MMDDYY or YYMMDD, I would rather it just assume that it was entered MMDDYY. Due to the program that my colleagues and I use, that is the format they habitually enter. I am just tired of manually going in and correcting the format for them. If I can set up the formatting so that it assumes it is in MMDDYY and converts it to YYYYMMDD automatically, it would save me a lot of time.

 

[Rick Rothstein]

I don't need it to tell whether it was entered as MMDDYY or YYMMDD, I would rather it just assume that it was entered MMDDYY. Due to the program that my colleagues and I use, that is the format they habitually enter. I am just tired of manually going in and correcting the format for them. If I can set up the formatting so that it assumes it is in MMDDYY and converts it to YYYYMMDD automatically, it would save me a lot of time.

Since you want to physically change the value that your users enter, then you cannot use simple Cell Formatting... you will need to use event code instead.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("C")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[D_rhodes]

Since you want to physically change the value that your users enter, then you cannot use simple Cell Formatting... you will need to use event code instead.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("C")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

That worked perfectly! You are a gentleman and a scholar! Only question: If I want it to target column C and D, should the middle line read "If Not Intersect(Target, Columns("C,D")) Is Nothing Then" or should I create a second sub that has that line with D instead of C? In other words, is this right:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("C,D")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
  End If
End Sub

or should it look like this:

Private Sub Worksheet_Change(ByVal Target As Range)  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("C")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
  End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("D")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
  End If
End Sub

Or am I completely off base? It has been a while since I have done anything with VB... Thanks so much for all your help, and Happy New Year to you and yours!

Edit: never mind, Neither of those worked... My code brain is so rusty...

Edit2: AH HA! I needed to add in a second If statement after the end of the first! This is what worked:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("C")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
    If Not Intersect(Target, Columns("D")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
  End If
End Sub

 

[D_rhodes]

Since you want to physically change the value that your users enter, then you cannot use simple Cell Formatting... you will need to use event code instead.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("C")) Is Nothing Then
      If Len(Target.Value) < 8 Then Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

wait, now if I delete anything entered it pops up a debug window... Do I need some sort of "If Not" statement for the "If Len" statement?

 

[Rick Rothstein]

wait, now if I delete anything entered it pops up a debug window... Do I need some sort of "If Not" statement for the "If Len" statement?

Sorry, I did not think your users would be deleting something they already entered. Give this replacement code a try then...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Not Intersect(Target, Columns("C")) Is Nothing Then
      If Target.Value Like "#####" Or Target.Value Like "######" Then
        Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
      End If
    End If
  End If
End Sub

 

[D_rhodes]

Sorry, I did not think your users would be deleting something they already entered. Give this replacement code a try then...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Not Intersect(Target, Columns("C")) Is Nothing Then
      If Target.Value Like "#####" Or Target.Value Like "######" Then
        Target.Value = Format(CDate(Format(Target.Value, "00\/00\/00")), "yyyymmdd")
      End If
    End If
  End If
End Sub

You, sir, are flippin amazing. I cannot begin to explain how much time you have saved me! Thanks a million times over, and have a great new year!!