Stop auto correction of number into a date

The simplest solution to defeating this annoying date feature is

1)      Save your workbook defaults in the workbook template file “Book1.xtlm” in the XLSTART folder

a.       Search for the  XLSTART folder in explorer; every computer is different

b.      Paste a shortcut to the XLSTART folder on your desktop (you will thank me)

c.       You will probably edit all the defaults several times;  number format, gridlines on, horizontal display etc..; so the desktop shortcut helps

2)      Since you cannot create a worksheet template file in 2010 (you can in 2003 & 2007); then record  a macro to insert sheets with your defaults

a.       Give it a useful name like “InsertSheet_MyDefaults1”;

b.       so record macro, give it name, F11, select all, select your formats (number, etc…); stop recording

3)      Now when you want to enter dates instead of numbers you should block reformat the cells and format them to the date format you like best

a.       You could record a macro for that as well

4)      Store all your macros in “personnel.xlsm” and save in XLSTART

a.       If all your macros are in this folder in XLSTART; then they are available to every sheet when you enter excel; once again the desktop shortcut helps

Good luck

the easiest way to do this is:

1. set up a default worksheet template and save the template in the XLSTART directory of your computer.  It will be saved as "Book1.xltm"

Every computer is different so use search to find this folder and you will be happier if you make a shortcut to this directory on your desktop.  

then save all the default formats you like (like your most commonly used number format for every cell or gridlines on, or horizontal format.....etc.) in the "Book1.xltm" workbook.  You keep customizing it. 

Now everytime excel creates a new notebook it start with YOUR default format and not MICROSOFTS most annoying defaults.

2. Excel 2010 is a pain in that you cannot set sheet formats the same way (excel 2003 & 2007 allow this);  Here is the work around:  record a macro that simply insert sheets with all your formats;  give it a useful name like "InsertSheet_MyFormat" or "InsertSheet_Format7".  Then instead of inserting sheets with F11 or the tab insert, you click on this macro from your developer tab to insert a new sheet.  The worksheets now have your defaults

3)  if you want this macro available in every workbook, then create a workbook "personnel.xlsm"

and store your macros in this file.  Save the file in the XLSTART directory; then this file opens first whenever you enter excel.   

4. if you ever want to enter dates, then block those cells and format in your favorite date format

good luck.

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