Excel - date is a number 1

[doyle9732]

I have looked in the FAQs and search this, but I cannot seem to find the answer on how to change it! I have a column of dates and times - in the fx window, I see them as their dates and times in the proper format, but in the column they show as whatever number since Jan 1, 1990. I've tried formatting the cell, and it doesn't change. This is a 2007 Excel document. Can someone show me the way!?
Stephanie

 

[Gavona]

You format the cells to display in the way you want (Format, Cells, Number, Date...). Usually best to keep as numbers so that excel can manipulate them. E.G. you can summarise by month, or add 1day or find how many days between two dates.

There is an FAQ on this site about them: faq68-5827

If you want to change them to text then have a look at the Text function. The format text takes the same form as wnen you format the cell.





















Gavin

 

[doyle9732]

Formatting doesn't work. I ended up copying and pasting the cells into a new sheet and every appeared correctly. The original must have had some sort of formatting on it that wouldn't be modified (or detected!).... although I'm still curious why I couldn't manage to change it! Will keep fiddling and report if I find the answer.

 

[SkipVought]

Hi,

Chances are these LOOK like numbers, but are really TEXT.

Here's a quik fix.
[tt]
Enter a 1 in an empty cell

Copy the cell containing the 1

Select the cells that should have Date Values (do not select the entire column!)

Edit > Paste Special - MULTIPLY

Now format as a DATE

VOLA!
[/tt]







Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[doyle9732]

Hi Skip,

I tried your instructions, but it didn't do anything. There is another column with time that displays as ie 0.06875 so I'm pretty sure I'm dealing with numbers.

Thanks for the suggestion though.
Stephanie

 

[doyle9732]

hmmm....another fact I'll throw out there....when I double click in the individual cell, it changes to the proper date, for example:

First Date cell shows as: 39689 and when I double click in that cell it changes to 08/29/2008. Then when focus is taken off that cell, it changes back to 39689.

Stephanie

 

[SkipVought]

Tools > Options > View Tab - uncheck FORMULAS

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[doyle9732]

Skip! You get a gold star! That did the trick! Thanks so much
Stephanie

 

[SkipVought]

Sorry, no GOLD stars here! ;-)

Glad you got a solution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[doyle9732]

yup.... it's been a while since I posted - thought they were gold .... a PURPLE star for you!

thanks again!
Stephanie