Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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
1  
You have to change it to text before you enter values. See my answer. –  David Zemens Feb 14 '13 at 6:09
2  
Simplest way is to type '1-2 - the ' tells Excel to treat the entry as text –  chris neilsen Feb 14 '13 at 7:07
add comment

migrated from stackoverflow.com Feb 14 '13 at 10:02

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

1 Answer

up vote 1 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 = "@"
Next

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 lmgtfy.com/?q=excel+2010+disable+autoformat+date, more info from Microsoft MVPs here, this is NOT really an autocorrect issue: answers.microsoft.com/en-us/office/forum/office_2007-excel/… –  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
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.