Stop auto correction of number into a date

This works for Excel 2010 , I think it will work with 2007

Using the data from the web query

ALT then A, R, O ........................ (Data -> Refresh All Drop down -> Connection Properties)

ALT+D ...................................... (Definition Tab)

ALT +E ...................................... (Edit Query)

ALT +O ..................................... (Options)

ALT +D ...................................... (Disable date recognition)

There may be greater ones, but it appears these minds think a like! :)

The topic of discussion is really a MS heavy blunder that annoys for decade. However, even if we live to new Excel versions with automatic reformatting made facultative, some of us will still have to work with preceding versions, which behave the present way. The solutions here mentioned for xl 2007 or higher, are probably inapplicable in older versions.

For some time, I was using a simple macro to correct the results of web queries, at least post fest. The present thread has provoked me to accomplish it with further amendments.

Following macros revert in fact the MS algorithms. The problem is that all formats like 1.12 or 1-12 are reformatted into the same shape of Jan-12 or 12.1, according to national setting, regardless of the original form. The back route thus has to be differentiated according the target, by choosing appropriate macro, here WrongDates2General or WrongDates2Scores (even fractions can be recreated to some extent by such a sort of macro). This implies also proper pre-selection of the different ranges to be re-transformed before running the macro (some real dates can even be originally ok and thus avoided).

The detection of the way the formats should be changed in, is derived from formats like “mmm-yy”, “yy-mmm”, “d-mmm” or “mmm-d”, xl automatically applies according to national settings. If these had been user changed, the cases of formats in Case rows in the script must be adapted.

The simultaneous function rendered by WrongDates2General macro, is the conversion of transferred numerals from the point to the comma style, including wrongly date-formatted, and vice versa, according to the automatically recognized national setting.

Tested with win7, xl 2003.

Regards

Petr Bezucha

Option Explicit

Sub FalseDate2Number()

Dim Cell As Range, DC As Variant, DS(0 To 1) As String, I As Long

DS(0) = "."

DS(1) = ","

I = Application.International(xlDecimalSeparator) = "."

For Each Cell In Selection

  DC = Cell.Value

  If VarType(Cell) = vbDate Then

    Select Case Cell.NumberFormat

    Case "d-mmm", "mmm-d"

      Cell.Value = CDbl(Day(DC) + Month(DC) / 10 ^ Len(CStr(Month(DC))))

    Case "mmm-yy", "yy-mmm"

      Cell.Value = CDbl(Month(DC) + 0.01 * CLng(Right(CStr(Year(DC)), 2)))

    End Select

  Else

    If IsNumeric(Replace(DC, DS(-I), DS(1 + I))) Then _

      Cell.Value = CDbl(Replace(DC, DS(-I), DS(1 + I)))

  End If

  Cell.NumberFormat = "General"

Next Cell

End Sub

Sub FalseDate2Score()

Dim Cell As Range, DC As Variant, DS(0 To 1) As String, I As Long, _

  A1 As String, A2 As String

Const Divider As String = "-" 

DS(0) = "."

DS(1) = ","

I = Application.International(xlDecimalSeparator) = "."

For Each Cell In Selection

  DC = Cell.Value

  If VarType(Cell) = vbDate Then

    Select Case Cell.NumberFormat

    Case "d-mmm", "mmm-d"

      A1 = CStr(Day(DC)): A2 = CStr(Month(DC))

    Case "mmm-yy", "yy-mmm"

      A1 = CStr(Month(DC)): A2 = Right(CStr(Year(DC)), 2)

    End Select

    Cell.NumberFormat = "@"

    Cell.Value = A1 & Divider & A2

  End If

Next Cell

End Sub