13

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.

Improve this question
5
  • 1
    Select cells, right-click on them and change cell format from General to Text
    – Mikhail Vladimirov
    Feb 14, 2013 at 6:05
  • Hour range means 1AM -2AM?
    – user187182
    Feb 14, 2013 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, 2013 at 6:07
  • 2
    You have to change it to text before you enter values. See my answer.
    – David Zemens
    Feb 14, 2013 at 6:09
  • 4
    Simplest way is to type '1-2 - the ' tells Excel to treat the entry as text
    – chris neilsen
    Feb 14, 2013 at 7:07

4 Answers 4

Reset to default
3

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
Improve this answer
7
  • 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, 2013 at 6:14
  • @forums "custom format @" - it's a symbol for Text format in VBA))
    – Peter L.
    Feb 14, 2013 at 8:09
  • 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, 2013 at 14:01
  • I feel your pain with excel. My boss just wanted to do everything in excel when I was insisting to use access :sigh again:. Also do you think the reason it reverts to date is because the adjacent cell is in date format?
    – forums
    Feb 14, 2013 at 14:10
  • can't say for sure about the revert issue, but Excel (and other MS products, for that matter) have been known to be a bit user-unfriendly when trying to "intelligently" do things, like formatting (Excel), indenting (Word), etc.
    – David Zemens
    Feb 14, 2013 at 14:22
4
  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
Improve this answer
2
  • Not happy about having to take the extra step of copy and pasting into notepad then re-pasting into excel. It's the only way my "dashed" entries don't convert to a date. The dashed entries are coming from a java table from an outside source. No other work around has stopped the conversion other than going through the notepad step.
    – CSmith
    Jul 30, 2017 at 20:28
  • Another way I use is to put ="1-2" as I have needed the same range info for stuff before - downside is it does not work in calculations...
    – Solar Mike
    Jul 30, 2017 at 20:39
0
  1. Select all your '.csv' data and copy to notepad
  2. Set all cells in new worksheet in Excel to text
  3. Paste all your data from Notepad as text
  4. Copy the "affected" column back to Notepad
  5. Change all your . to , (e.g. 10.00 to 10,00)
  6. Open new Excel book - IMPORTANT
  7. Copy your column from Notepad to this new Excel book
  8. Copy and paste the column back to your original Excel book

The only drawback is that you have your numbers now with (,) instead of (.).

Improve this answer
0

Another way is:

  1. save sheet as CSV
  2. rename it to .txt
  3. reopen it in Excel
  4. import module is invoked
  5. switch from general to text format for each numeric column
Improve this answer

You must log in to answer this question.

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