date formatting broken in XL2003 SP3?

[Salgud]

I have a spreadsheet in which the dates have been entered in various
formats. I tried to change them all to the same format, didn't really
matter a lot which one, but most of them didn't change at all. Does anyone
know if date formatting is broken? If not, what is the trick to get it to
work?

 

[cai.ti]

I have a spreadsheet in which the dates have been entered in various
formats. I tried to change them all to the same format, didn't really
matter a lot which one, but most of them didn't change at all. Does anyone
know if date formatting is broken? If not, what is the trick to get it to
work?

Hi

Check to see if the dates are numbers by changing the format to
number. eg today 23 Nov 2010 is 40505.
I suspect you have a mashup of text dates and alsorts in there.

If it looks like a date and has all the right info it is a simple
matter of a bit of text manipulation to get it to be a date by using
the DATE function.

A bit more info I could help more.

M

 

[Gord Dibben]

Some of the dates are prpbably Text and not true dates.

Format all to General.

Those that are real dates will turn into 4 digit numbers like 40505 for November
23, 2010

You may have to use the DATE function on Text dates.

One other trick is to run then through Data>Text to Columns and in third step
format column as Date DMY or whatever.


Gord Dibben MS Excel MVP

 

[Salgud]

Some of the dates are prpbably Text and not true dates.

Format all to General.

Those that are real dates will turn into 4 digit numbers like 40505 for November
23, 2010

You may have to use the DATE function on Text dates.

One other trick is to run then through Data>Text to Columns and in third step
format column as Date DMY or whatever.


Gord Dibben MS Excel MVP

Thanks to both of you - that fixed it!

 

[Gord Dibben]

Good to hear.

BTW

40505 is NOT a 4 digit number<g>


Gord

 

[Salgud]

Good to hear.

BTW

40505 is NOT a 4 digit number<g>


Gord

I just figured you forgot to count with your thumb!