Numbers convert to date format (Excel 2000)

Topic

New Reply

I’ve got a column with a range in it. For instance, 1-10. When I type this, Excel wants to convert it to a date January 10, 2002. When I change the formatting back to General Number it changes to the equivalency of the date 37266. Is there any other way to fix this other than using Text formatting?

Reply | Quote

Replies

I’m not sure what you are using the cell for, so this may not work but if you type the 1-10 with a single quotation mark preceding it….. ‘1-10 then it will display as 1-10.
HTH
Stats

Reply | Quote

Stats approach is the simplest. FWIW, sometimes when I’m using a series of corridor ranges which are from another source within the workbook, I use a formula like this:

=TEXT(corridor_floor_cell,”format”)&” – “&TEXT(corridor_ceiling_cell,”format”)

Reply | Quote

Since 1-10 is not a numeric value (it is either a date or it is a formula that evaluates to -9), then the answer is no, you can not do this without having it in text format. There are two ways to do this:

1- Insert a single quote in front of the range when you enter it like this: ‘1-10
The single quote will not display.

2- Format the cell to text before you enter the range into it.

Reply | Quote