Prevent automatic conversion of numbers and hyphens into dates

Excel's enhansement to expedite entering a date and formatting the cell as a date has some limitations. If a cell has two or three numbers delimited by hyphens it may be converted to a date. The logic uses the range of the numbers to determine the date and if a date was specified.

This conversion is a problem when downloading sport websites with tables containing numbers delimited by hyphens. Importing Web sites has an option,".WebDisableDateRecognition = True", to disable date recognition that can be included in the VBA import statement. When copying files the DateRecognition is functional. If possible to circumvent DateRecognition the receiving cells must be set to text format.

The following code maps the date recognition changed cells to its original but its not a one to one mapping. Although most of the mapping is unique a few examples of duplicate entires generating the same date are listed below.

Entering two numbers and a hyphen to generated a date

The first number is the month and must be 1 to 12. The second number must be 0 to 99. When the second number is an actual day in the specified month. It is used as the day of the month in the current year with the date format d-mmm. When the second number is 0 the year of the date is 2000 in the format mmm-yy.

When the second number is larger than an actual day in the specified month it is used as a year in the 20 century with the date format mmm-yy.

   Examples of two numbers and a hyphen to generated a date
   Enter the string "12-0"  cell is set to Dec-00 year=2000	with format mmm-yy
   Enter the string "12-2"  cell is set to 2-Dec  year=2010 with format d-mmm
   Enter the string "12-31" cell is set to 31-Dec year=2010 with format d-mmm
   Enter the string "12-32" cell is set to Dec-32 year=1932 with format mmm-yy

Entering three numbers and two hyphens to generated a date

The first number is the month and must be 1 to 12. The second number is an actual day in the specified month. The third number is the year and can be two or four digits. The two digit years of 0 to 29 is converted to 2000 to 2029 and 30 to 99 it converted to 1930 to 1999. When the numbers are in the specified ranges there is a conversion to a date with the date format m/d/yyyy.

Since the year in the third number can be specified by two digits or four digits two strings of three numbers and two hyphens are mapped into the same date with for the format m/d/yyyy. The macro to restore the numbers and hyphens will generate the third number as four digits. To restore the three numbers as two digits change the format of the column from m/d/yyyy to m/d/yy before running the macro to resore the numbers and hyphens from the date.

   Examples of duplicate entires generated a date
   Enter the string "12-2-0"     cell is set to 12/2/2000 with format m/d/yyyy
   Enter the string "12-2-2000"  cell is set to 12/2/2000 with format m/d/yyyy
   Enter the string "12-2-99"    cell is set to 12/2/1999 with format m/d/yyyy
   Enter the string "12-2-9999"  cell is set to 12/2/1999 with format m/d/yyyy

   Examples of three numbers and two hyphens and year is two digits to generated a date
   Enter the string "12-2-0"    cell is set to 1/2/2000 with format m/d/yyyy
   Enter the string "12-2-29"   cell is set to 1/2/2029 with format m/d/yyyy
   Enter the string "12-2-30"   cell is set to 1/2/1930 with format m/d/yyyy
   Enter the string "12-2-99"   cell is set to 1/2/1999 with format m/d/yyyy

   Examples of three numbers and two hyphens and year is four digits to generated a date
   Enter the string "1-1-1900"   cell is set to 1/1/1900   with format m/d/yyyy
   Enter the string "12-31-1999" cell is set to 12/31/1999 with format m/d/yyyy
  String     <>  Reset     <>  Conversed  <>  format    <>  year 
 "2-0"       <>  2-0       <>   Feb-00    <>  mmm-yy    <>  2000 
 "2-1"       <>  2-1       <>  	1-Feb     <>  d-mmm     <>  2011 
 "2-2"       <>  2-2       <>  	2-Feb     <>  d-mmm     <>  2011 
 "2-28"      <>  2-28      <>  	28-Feb    <>  d-mmm     <>  2011 
 "2-29"      <>  2-29      <>  	Feb-29    <>  mmm-yy    <>  2029 
 "2-30"	     <>  2-30      <>  	Feb-30    <>  mmm-yy    <>  1930 
 "2-98"	     <>  2-98      <>  	Feb-98    <>  mmm-yy    <>  1998 
 "2-99"      <>  2-99      <>  	Feb-99    <>  mmm-yy    <>  1999 
 "2-5-1900"  <>  2-4-1900  <>  	2/5/1900  <>  m/d/yyyy  <>  1900 
 "2-5-1901"  <>  2-5-1901  <>  	2/5/1901  <>  m/d/yyyy  <>  1901 
 "2-5-9998"  <>  2-5-9998  <>  	2/5/9998  <>  m/d/yyyy  <>  9998 
 "2-5-9999"  <>  2-5-9999  <>  	2/5/9999  <>  m/d/yyyy  <>  9999 
 

Macro to prevent automatic conversion of numbers and hyphens into dates

Sub RESET_DATE_CELLS_CAUSED_BY_HYPHEN()
   'Reset cells with a date format to a string with 2 or 3 numbers delimited by a hyphen
   'The first number is the month and must be 1 to 12
   'There are 1200 two numbers delimited by a hyphen will create a dated cell
   'There are 2,956,500 three numbers delimited by 2 hyphens will create a dated cell
   'Interactive selection of columns with date formats is available
   'Global variable DEFUALTVLUE = "ALL" to select all columns
   'Global variable DEFUALTVLUE = "A,C,E" to select columns A, C AND E
   'Uses function NUMBER_TO_LETTERS
   'Required Public Const ABC As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim MOVIT(100, 20)
    Dim DATE_CELLS_IN_COLUMN(256, 25)
    SUBNAME = "Reset dated cells to hyphen delimited numbers"
    If TypeName(ActiveWorkbook) = "Nothing" Then
       MsgBox "There are no active workbooks" & Chr(10) & "Routine exits", , SUBNAME
       Exit Sub
    End If
    RESPONSE = ""
    If DEFAULTVALUE <> "" Then
       RESPONSE = DEFAULTVALUE
       DEFAULTVALUE = ""
    End If
    Cells(1, 1).Select
    Call GET_LAST_NON_BLANK_COLUMN
    Cells(1, 1).Select
    Call GET_LAST_NON_BLANK_ROW
   'Create an array of every column with a date formated cell
   'Array DATE_CELLS_IN_COLUMN contains
   '1. column number       2. column letter,     3. number of dated cells,
   '4. first row with date 5. last row with date
    For JJ = 1 To NUMCOLS
        DATE_CELLS_IN_COLUMN(JJ, 4) = NUMROWS '66000
        DATE_CELLS_IN_COLUMN(JJ, 5) = 0
        For II = 1 To NUMROWS
            CELL = Cells(II, JJ)
            If TypeName(CELL) = "Date" Then
               DATE_CELLS_IN_COLUMN(JJ, 1) = JJ
               DATE_CELLS_IN_COLUMN(JJ, 2) = NUMBER_TO_LETTERS(JJ)
               DATE_CELLS_IN_COLUMN(JJ, 3) = DATE_CELLS_IN_COLUMN(JJ, 3) + 1
               If DATE_CELLS_IN_COLUMN(JJ, 4) > II Then DATE_CELLS_IN_COLUMN(JJ, 4) = II
               If DATE_CELLS_IN_COLUMN(JJ, 5) < II Then DATE_CELLS_IN_COLUMN(JJ, 5) = II
            End If
        Next
    Next
    NUM = 0
    For JJ = 1 To NUMCOLS
        If DATE_CELLS_IN_COLUMN(JJ, 1) > 0 Then
           NUM = NUM + 1
           If SELECTED_COLUMNS = "" Then
              SELECTED_COLUMNS = DATE_CELLS_IN_COLUMN(JJ, 2)
           Else
              SELECTED_COLUMNS = SELECTED_COLUMNS & ", " & DATE_CELLS_IN_COLUMN(JJ, 2)
           End If
           DATE_CELLS_IN_COLUMN(NUM, 1) = DATE_CELLS_IN_COLUMN(JJ, 1)
           DATE_CELLS_IN_COLUMN(NUM, 2) = DATE_CELLS_IN_COLUMN(JJ, 2)
           DATE_CELLS_IN_COLUMN(NUM, 3) = DATE_CELLS_IN_COLUMN(JJ, 3)
           DATE_CELLS_IN_COLUMN(NUM, 4) = DATE_CELLS_IN_COLUMN(JJ, 4)
           DATE_CELLS_IN_COLUMN(NUM, 5) = DATE_CELLS_IN_COLUMN(JJ, 5)
        End If
    Next
    NUMBER_COLUMNS_WITH_DATE_CELLS = NUM
    If NUMBER_COLUMNS_WITH_DATE_CELLS = 0 Then
       MSG = "There were no columns with a date formated cell in the active spreadsheet "
       MSG = MSG & ActiveSheet.Name & Chr(10)
       MSG = MSG & "in workbook " & ActiveWorkbook.Name
       MsgBox MSG, , SUBNAME
       Exit Sub
    End If
    If UCase(RESPONSE) = "ALL" Then GoTo DOIT  'All dated columns were requested to be reset
   'Varify the columns passed to routine in response contain date formats
    If RESPONSE <> "" Then
      'Split response by comma from DEFAULTVALUE to select the columns using a dymanit array
       MAC_STRS = Split(RESPONSE, ",")
       For II = 0 To UBound(MAC_STRS)
           MAC_STRS(II) = Trim(MAC_STRS(II))
       Next
      'Indicate the seleted columns
       NUM = 0
       MSG = "DEFAULTVALUE FOUND=" & RESPONSE & Chr(10) & Chr(10)
       MSG = "Columns selected with dated cells=" & Chr(10) & Chr(10)
       For II = 1 To NUMBER_COLUMNS_WITH_DATE_CELLS
           Found = 0
           For KK = 0 To UBound(MAC_STRS)
               If DATE_CELLS_IN_COLUMN(II, 2) = MAC_STRS(KK) Then Found = 1
           Next
           If Found = 1 Then
              NUM = NUM + 1
              For JJ = 1 To 5
                  DATE_CELLS_IN_COLUMN(NUM, JJ) = DATE_CELLS_IN_COLUMN(II, JJ)
              Next
              MSG = MSG & DATE_CELLS_IN_COLUMN(NUM, 2) & Chr(10)
           End If
       Next
       NUMBER_COLUMNS_WITH_DATE_CELLS = NUM
      'MsgBox "NUMBER_COLUMNS_WITH_DATE_CELLS=" & NUMBER_COLUMNS_WITH_DATE_CELLS & _
              Chr(10) & Chr(10) & MSG, , SUBNAME
       If NUMBER_COLUMNS_WITH_DATE_CELLS > 0 Then GoTo DOIT
       MsgBox "NUMBER_COLUMNS_WITH_DATE_CELLS=" & NUMBER_COLUMNS_WITH_DATE_CELLS & _
             Chr(10) & Chr(10) & "DEFAULTVALUE FOUND=" & RESPONSE, , SUBNAME
       Exit Sub
    End If
   'interaction is required
   'Generate sheet to be displayed when interaction is required to adjust column selection
   'Generate a sheet with the array variable DATE_CELLS_IN_COLUMN in a new workbook
    Workbooks.Add
    BOOK_NAME = ActiveWorkbook.Name
    NUM = 0
    For II = 1 To NUMBER_COLUMNS_WITH_DATE_CELLS
        For JJ = 1 To 5
           Cells(II, JJ) = DATE_CELLS_IN_COLUMN(II, JJ)
        Next
    Next
    Rows("1").Select
    Selection.Insert Shift:=xlDown
    Cells(1, 1) = "Column number"
    Cells(1, 2) = "Column"
    Cells(1, 3) = "Number of date cells in column"
    Cells(1, 4) = "First row with date cells in column"
    Cells(1, 5) = "Last row with date cells in column"
    Cells(1, 6) = "SELECTED_COLUMNS"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells(1, 1).Select
    ActiveWorkbook.SAVED = True
   'Seleted the columns to reset the dates to its previous format
    If RESPONSE = "" Then
       MSG = "Do you want to reset the " & NUMBER_COLUMNS_WITH_DATE_CELLS & " "
       MSG = MSG & "columns with date formated cells to hyphen delimited numbers?"
       MSG = MSG & Chr(10) & Chr(10)
       MSG = MSG & "You can select the columns you want to reset "
       MSG = MSG & "with the comma delimited string"
       RESPONSE = InputBox(MSG, SUBNAME, SELECTED_COLUMNS, 3000, 3000)
    Else
       RESPONSE = SELECTED_COLUMNS
    End If
   'Clean response
    If RESPONSE = "" Then Exit Sub
    RESPONSE = UCase(Trim(RESPONSE))
    If Mid(RESPONSE, Len(RESPONSE), 1) = "," _
    Then RESPONSE = Mid(RESPONSE, 1, Len(RESPONSE) - 1)
    If RESPONSE = "" Then Exit Sub
   'Split response by comma to select the columns using a dymanit array
    MAC_STRS = Split(RESPONSE, ",")
    MSG = "Response=" & RESPONSE & Chr(10) & Chr(10) & "MAC_STRS=" & Chr(10)
    For II = 0 To UBound(MAC_STRS)
        MAC_STRS(II) = Trim(MAC_STRS(II))
        MSG = MSG & II & ". " & MAC_STRS(II) & Chr(10)
    Next
   'MsgBox MSG
   'Indicate the seleted columns
    For II = 1 To NUMBER_COLUMNS_WITH_DATE_CELLS
        Found = 0
        For KK = 0 To UBound(MAC_STRS)
            If DATE_CELLS_IN_COLUMN(II, 2) = MAC_STRS(KK) Then Found = 1
        Next
        If Found = 1 Then Cells(II + 1, 6) = "Selected"
    Next
   'sort the macro variable DATE_CELLS_IN_COLUMN
    Cells.Select
    Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes
    NUM = 0
   'save the seleted columns
    For II = 1 To NUMBER_COLUMNS_WITH_DATE_CELLS
        If Cells(II + 1, 6) = "Selected" Then
           NUM = NUM + 1
           For JJ = 1 To 5
               DATE_CELLS_IN_COLUMN(NUM, JJ) = Cells(II + 1, JJ)
           Next
        End If
    Next
    NUMBER_COLUMNS_WITH_DATE_CELLS = NUM
    Windows(BOOK_NAME).Activate
    ActiveWorkbook.SAVED = True
    ActiveWorkbook.Close
DOIT:
   'Reset cells in Seleted columns with dates to a hyphenated string with 2 or 3 number
    For KK = 1 To NUMBER_COLUMNS_WITH_DATE_CELLS
        JJ = DATE_CELLS_IN_COLUMN(KK, 1)
        For II = DATE_CELLS_IN_COLUMN(KK, 4) To DATE_CELLS_IN_COLUMN(KK, 5)
            Cells(II, JJ).Select
            CELL = Cells(II, JJ)
            TYPE_NAME = TypeName(CELL)
            If TYPE_NAME = "Date" Then
               FORMAT_OF_CELL = ActiveCell.Cells.NumberFormat
               DAY_OF_CELL = Day(CELL)
               MONTH_OF_CELL = Month(CELL)
               YEAR_OF_CELL = Year(CELL)
               CENTURY = Val(Left(YEAR_OF_CELL, 2) & "00")
               If FORMAT_OF_CELL = "d-mmm" Then
                  CELL = MONTH_OF_CELL & "-" & DAY_OF_CELL
                  Selection.NumberFormat = "@"
                  Cells(II, JJ) = CELL
               ElseIf FORMAT_OF_CELL = "mmm-yy" Then
                  CELL = MONTH_OF_CELL & "-" & YEAR_OF_CELL - CENTURY
                  Selection.NumberFormat = "@"
                  Cells(II, JJ) = CELL
               ElseIf ActiveCell.Cells.NumberFormat = "m/d/yy" Then
                     YEAR_2DIGITS = Val(Right(YEAR_OF_CELL, 2))
                     CELL = MONTH_OF_CELL & "-" & DAY_OF_CELL & "-" & YEAR_2DIGITS
                     Cells(II, JJ).Select
                     Selection.NumberFormat = "@"
                     Cells(II, JJ) = CELL
               ElseIf ActiveCell.Cells.NumberFormat = "m/d/yyyy" Then
                     CELL = MONTH_OF_CELL & "-" & DAY_OF_CELL & "-" & YEAR_OF_CELL
                     Cells(II, JJ).Select
                     Selection.NumberFormat = "@"
                     Cells(II, JJ) = CELL
                     If YEAR_OF_CELL = 1899 And MONTH_OF_CELL = 12 Then
                        Cells(II, JJ) = "1-1-1900"
                     End If
                     If YEAR_OF_CELL = 1900 And MONTH_OF_CELL = 1 Then
                        If DAY_OF_CELL = 32 Then
                         Cells(II, JJ) = "2-1-1900"
                       Else
                           Cells(II, JJ) = "1-" & DAY_OF_CELL + 1 & "-" & YEAR_OF_CELL
                        End If
                     End If
                     If YEAR_OF_CELL = 1900 And MONTH_OF_CELL = 2 Then
                        Cells(II, JJ) = "2-" & DAY_OF_CELL + 1 & "-" & YEAR_OF_CELL
                     End If
               End If              
            Else
               YEAR_OF_CELL = Null
               DAY_OR_YEAR_OF_CELL = Null
               FOUR_DIGIT_YEAR = Null
            End If
        Next
    Next
    Cells(DATE_CELLS_IN_COLUMN(1, 4), DATE_CELLS_IN_COLUMN(1, 1)).Select
ENDIT:
ActiveWorkbook.SAVED = True
End Sub