Date formating problem?

Hi,

faq68-5827

345664 is the DateSerial value.

Simply celect the range and Format > Cells - Select Number Tab and select a Date DISPLAY Format.

Just remember that Date/Time values are really NUMBERS that can be FORMATTED to DISPLAY any number of ways.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

Well,
I did what you said before and after you told me, but this doen't do anyhing!
This is very strange as usually it works without any problems.
For now my date 6/22/2007 will only shows as 39255. Whatever I do to it- Custom formatting, date formatting, etc.
It will show me the correct date tho.

EDIT the value and ENTER.

See what happens.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

Nothing.
If I type someting that looks like a date (ex:05/02/06) it will convert to the number.
If I type a number, it keep the number without try to convert to a date in thr uppper windows.
I use Excel all the time, but I never had this

What does this mean?

"For now my date 6/22/2007 will only shows as 39255. Whatever I do to it- Custom formatting, date formatting, etc.
It will show me the correct date tho."

I am confused!

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

Are you ABSOLUTELY SURE that you EDITED the cell in question???

I can...
[tt]
FORMAT a cell as TEXT
Enter 39255
Change the FORMAT to Date
[/tt]
and NOTHING CHANGES....

unitl...

I EDIT the cell (F2) then hit ENTER. The EDIT causes the Date FORMAT to take effect over the Text FORMAT.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

It meanS that I already reformated so many times.... but it doesn't do anything.

Well, I make it work. How I did iit....
I cut and paste the whole sheet and reinserted in another sheet. By miracle it is OK!!!!
I really would like to know why!

Thanks for your help, just got luky on my side, but i do not like to get lucky, I would prefer to know why.
ThANKS AGAIN.

Skip's suggestion of formatting as a number, then pressing [F2], then pressing [Enter] [!]will[/!] work.

Is the data on the original sheet that was giving you trouble imported from some other application? I ask because this is a common problem with imported data. The cause is that the data is formatted as text. Even though you might have tried to change the formatting, even though you can check the formatting of the cells and Excel says they are formatted as number, they are really still formatted as text.

But the following steps will fix it for you:
[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble[sup][red]*[/red][/sup][/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

[sup][red]*[/red][/sup] Note that any null cells that you select will be converted to zeros. For this reason I recommend that you don’t select an entire row/column.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

SkipVought:

When I do F2, I can see the right date in the field, when I press 'Enter', it goes back to the number.


anotherhiggins:
Tried this, but to no effect. I noticed that any format I want to apply to any cell on this sheet is not working.
I will assume that something is 'dead' o this specific sheet. The cut and paste to another sheet worked.
Thanks for the infos.

Hi TTA23,

Go into Tools|Options|View and uncheck 'formulas'

Cheers

[MS MVP - Word]