formatting - How to disable Excel number autoformat

Monday 5 August 2019

This problem is simple to correct but is very very annoying
what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on per cell row

DATE --- HOUR
1/1/2013 0-1
1/1/2013 1-2
1/1/2013 2-3
1/1/2013 3-4

Excel stupidly keeps formatting 1-2 to a date 2-Jan
to make matters worse is when I change it to a number format
it gives 41276

I also cannot find the AutoFormat option in excel 2010, I am aware of the Format button in home but there is no autoformat there.

Answer

Before you enter any values in the column/cells, change the cell format to Text.

Or, put this in the Workbook code module to force this formatting on all sheets, each time the workbook is opened.

Private Sub Workbook_Open()
Dim sh As Worksheet

For Each sh In Me.Sheets
    sh.Cells.NumberFormat = "@"
Next

End Sub

Notes