Date Formats don't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel spreadsheet with a column of dates in the MM/DD/YYYY format. When I click on Format, Cells and Choose Category of Date and set it to the YYYY-MM-DD format, nothing changes. In fact if I set it to any date format, the format of the cells do not change. If I enter a value of '05/01/2003' and choose date format of YYYY-MM-DD, it does not change the format. What gives

My regional settings are US, but I do have my date format settings as 'YYYY-MM-DD' with the hyphen as the separator.
 
The values are probably text

--

Regards,

Peo Sjoblom


TdyYrLove said:
I have an Excel spreadsheet with a column of dates in the MM/DD/YYYY
Click to expand...
format. When I click on Format, Cells and Choose Category of Date and set
it to the YYYY-MM-DD format, nothing changes. In fact if I set it to any
date format, the format of the cells do not change. If I enter a value of
'05/01/2003' and choose date format of YYYY-MM-DD, it does not change the
format. What gives?
My regional settings are US, but I do have my date format settings as
Click to expand...
'YYYY-MM-DD' with the hyphen as the separator.
 
-----Original Message-----
I have an Excel spreadsheet with a column of dates in the MM/DD/YYYY format.
When I click on Format, Cells and Choose Category of
Click to expand...
Date and set it to the YYYY-MM-DD format, nothing
changes.
In fact if I set it to any date format, the format of the cells do not change.
If I enter a value of '05/01/2003' and choose date
Click to expand...
format of YYYY-MM-DD, it does not change the format.

Are you sure that the format has been set to Date before
you entered the values? Otherwise, it might be that the
value is stored as Text, and in that case no change of
format will help. To translate a Text column to Date,
select the whole column, and do Data / Text to
Columns... / Delimited (you do not need choose any
delimiter, because you need just one column) / Date(MYD)

hope this helps
 
The Text to Columns does the trick

It appears to be a problem with an incorrect date format. The cell is defined as a date column with a format of YYYY-MM-DD. So if I enter 03/01/1999, it does not recognize that as a date (1999 is greater than 31). The format will not convert one order to another (MDY to YMD), but it will convert format (1999/01/01 to 1999-01-01)

Thanks for the help!
 
Back
Top