2007 text to date - entire sheet

[amirf]

Hi,
I have a data sheet which has one date column.
I formatted the entire column to be in date format.
Then, for some reason, I opened the sheet and got all the column as text. for examle: 30/04/2014 shows as 41759. i tried re-formatting the cells and the column, but nothing helps, it stills shows the dates as text. + when i double click the cell, it show in normal date format. i thought my entire excel rules changed some how, but other excel fiels, or new ones behave normally.
can anyone tell me what went wrong and how to fix it?
thanks a lot!
Amir

 

[britsgal]

OK, if the cell where you have the date has the "text marker" in the cell, here's what you can do to make the cell not "text" and then reformat it to your date. In the cell next to the first "text" cell (lets say your "text" date is in column/cell A1, put your curser in B1) enter the formula =A1*1 and hit enter. These should turn your text "41759" back into general format and you should be able to reformat it as your date. Give it a try.

 

[amirf]

hi britsgal,
thank you for your reply!
I tried your suggested solution, but it does not work
looks like excel regards all cells as text
putting the formula in the required cell results in the cell showing the formula, not it's result. i also tried entering =2*2 expecteing the cell to display 4, but it remains 2*2
what happened to my sheet??
thanks!

Amir

 

[britsgal]

hi britsgal,
thank you for your reply!
I tried your suggested solution, but it does not work
looks like excel regards all cells as text
putting the formula in the required cell results in the cell showing the formula, not it's result. i also tried entering =2*2 expecteing the cell to display 4, but it remains 2*2
what happened to my sheet??
thanks!

Amir

with your cursor on the cell where it says 2*2 go to the data tab, then text to columns, select delimited and click finish. This will stop it from being text and you then should be able to copy the formula down.

 

[amirf]

with your cursor on the cell where it says 2*2 go to the data tab, then text to columns, select delimited and click finish. This will stop it from being text and you then should be able to copy the formula down.

i'm sorry, but this does not help either.. ):