Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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.

share|improve this question

migrated from Feb 14 '13 at 10:02

This question came from our site for professional and enthusiast programmers.

Select cells, right-click on them and change cell format from General to Text – Mikhail Vladimirov Feb 14 '13 at 6:05
Hour range means 1AM -2AM? – user187182 Feb 14 '13 at 6:06
tried changing it to text but it still keeps on insisting the date. And also using military format 1-24 so no am/pm there – forums Feb 14 '13 at 6:07
You have to change it to text before you enter values. See my answer. – David Zemens Feb 14 '13 at 6:09
Simplest way is to type '1-2 - the ' tells Excel to treat the entry as text – chris neilsen Feb 14 '13 at 7:07
up vote 3 down vote accepted

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 = "@"

End Sub
share|improve this answer
I think using the custom format @ solved it for me, the Text doesn't seem to work w/ch is weird. Also do you know of a way to disable/enable autoformatting function of excel? There used to be one in xl2003 – forums Feb 14 '13 at 6:14
@forums "custom format @" - it's a symbol for Text format in VBA)) – Peter L. Feb 14 '13 at 8:09
@forums, more info from Microsoft MVPs here, this is NOT really an autocorrect issue:… – David Zemens Feb 14 '13 at 13:07
I tried googling and knew about changing it to text but like my comment above, it keeps being reverted to date. Don't know why @ worked fine. Thanks for the ms forum link too, learned that autoformatting cannot be simply turned off :sigh: – forums Feb 14 '13 at 14:01
If you think that is difficult to work with, try working with different date formats! :) I had a client in the UK using UK date formatted CSV files which made troubleshooting difficult since my computer is set on US formats. It was ultimately a simple fix, but man, it was frustrating until I figured out how to switch between the two! – David Zemens Feb 14 '13 at 14:06
  1. cut and paste everything into note_pad
  2. delete what ever left in xl
  3. Select all needed cells, columns row to text format
  4. re-paste from note_pad
  5. if you need later you can change to date, time formats
share|improve this answer
This actually works great, thanks! – Steve Bauman Sep 18 '15 at 16:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .