Cell Format - Date

[Jerid B]

Excel 2003.
I have a previous made spreadsheet from someone.
The date cells are listed like this: 112807
The format is general. The date should be 11/28/07
However when I change the format to date it comes out like this: 11/07/08
How can I fix this? This is a huge spreadsheet so rekeying everything would
take a VERY long time.

Thanks in advance, Jerid

 

[Gord Dibben]

The format order has to do with your Windows Regional settings for shortdate.

Either change that setting or, within Excel, select the 112807 and Data>Text to
Columns>Next>Next>Column Data Format>Date>MDY and Finish.


Gord Dibben MS Excel MVP

 

[Conan Kelly]

Jerid,

What do the dates for January-September look like? 13107 (mddyy) or 013107
(mmddyy)?

What do the dates for the first 9 days of each month look like? 11107
(mmdyy) or 110107 (mmddyy)?

If "mmddyy" for all dates, then enter this in a blank helper column:

(I'm assuming your date column is column D starting in row 2, my helper
column is going to be column G)
In G2, use this formula:
=date(right(D2,2),left(D2,2),mid(D2,3,2))
Copy/fill G2 down to the end of the date list in column D
Select G2:G[last row number]
copy
select D2
paste special > values
format D2[last row number] to the desired date format
delete helper column (column G)

This will only work if you have both 2-digit months & 2-digit days. If
something else, we might be able to adjust it to get it to work.

HTH,

Conan