date formatting broken in XL2003 SP3?

  • Thread starter Thread starter Salgud
  • Start date Start date
S

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?
 
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?
Click to expand...

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
 
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
 
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
Click to expand...

Thanks to both of you - that fixed it!
 
Back
Top