VBA - converting date input DDMMYY to date

[relaxed_surfer]

Hi. How can I convert the input of 040210 to the correct date (4th of February 2010) in a date formated field? It gets converted by excel to 1st of Feb. 2010 (!). That's 40210 days after 1st of january 1900. So I don't know how to convert the input to the 4th og february 2010 without also converting the date 1st of February 2010. In Norway, users expect DDMMYY to be converted to the local date format in Excel.

I wrote some VBA code in the to convert different types of input like DDMMYYYY, DDMMYY, DD.MM.YYYY.
Here is an a very simplified code to convert DDMMYY to DD.MM.YYYY (Norwegian date format)

Dim DateFixed As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If DateFixed = True Then
        DateFixed = False
        Exit Sub
    End If
 
  'Date fix
  If Target.NumberFormat = "m/d/yyyy" Then
     ' If the input is of type DDMMYY with leading zero (Target.Value has length 5)
          DateFixed = True
          Target.Value = DateValue(Left(Target.Value2, 1) & "." & Mid(Target.Value2, 2, 2) & "." & Right(Target.Value2, 2))
     ' End If
  End If
End Sub

This works fine entering 040210, but it also converts the date 1st of February 2010 to the 4th of February 2010.

So excel does not differ between 040210 and the date 1st of February 2010:

Entering 040210:
Watch : - : Target : 01.02.2010 : Range/Range : Module1.dateFix
: Text : "01.02.2010" : Variant/String : Module1.dateFix
: Value2 : 40210 : Variant/Double : Module1.dateFix

Entering 01.02.2010 (Norwegian date format for 1st of February):
Watch : - : Target : 01.02.2010 : Range/Range : Module1.dateFix
: Text : "01.02.2010" : Variant/String : Module1.dateFix
: Value2 : 40210 : Variant/Double : Module1.dateFix


The problem is that the event Worksheet_Change is fired after Excel has done the conversion internally, so I need a way to capture the user input before this i done in Excel. Any suggestions?

I can't use a text field, since there's a lot of calculations done with the date fields, like calculating number of days for travel compensation, and so on.

 

[John_w]

This seems to work for DDMMYY input:

Dim DateFixed As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim sInput As String, dt As Date
    
    If DateFixed = True Then
        DateFixed = False
        Exit Sub
    End If
 
  'Date fix
  If Target.NumberFormat = "m/d/yyyy" Then
     ' If the input is of type DDMMYY with leading zero (Target.Value has length 5)
          DateFixed = True
          sInput = Target.Value2
          If Len(sInput) <> 6 Then sInput = "0" & sInput
          dt = DateSerial(Right(sInput, 2), Mid(sInput, 3, 2), Left(sInput, 2))
          Target.Value = dt
     ' End If
  End If
End Sub

 

[relaxed_surfer]

Hi, thank you for your attempt, but the problem is that this code converts the date 1st of February 2010 (enter it in your local language) to the 4th of February 2010.
There should be a way to capture in VBA if the user has entered 040210 or the 02/01/2010 (m/d/yyyy) , but I can't find out how..
Any ideas?

This seems to work for DDMMYY input:

Dim DateFixed As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim sInput As String, dt As Date
 
    If DateFixed = True Then
        DateFixed = False
        Exit Sub
    End If
 
  'Date fix
  If Target.NumberFormat = "m/d/yyyy" Then
     ' If the input is of type DDMMYY with leading zero (Target.Value has length 5)
          DateFixed = True
          sInput = Target.Value2
          If Len(sInput) <> 6 Then sInput = "0" & sInput
          dt = DateSerial(Right(sInput, 2), Mid(sInput, 3, 2), Left(sInput, 2))
          Target.Value = dt
     ' End If
  End If
End Sub

 

[Richard Schollar]

Personally, I think i would use some form of datepicker userform/control so that you are controlling what date is actually entered rather than allowing the possibility that a user enters a value which may have one date meaning to them but another to Excel. Either that, or make it utterly clear to users that a single format must be adopted for entering the date - then you can design code around that.