The danger you have is that some dates are mis-converted.
Some dates will be correct format, and will align to the right of the
column (widen the column to seperate the two formats)
Other dates are 'text' entries, and as such are not considered dates,
and align to the left of the column.
You can seperate these by entering in B1 (assuming your dates are in
column A, and column B is empty)
=IF(ISERROR(MONTH(A1)),A1,"")
and formula-copy that down your 4,000 odd lines.
This should give you a column of non-dates. (so to speak)
Copy column B and Paste Special, Values back over column B.
Highlight Column B and Data, Text to Columns, and select the correct
date format (DMY or MDY as the dates exist) and 'Finish'
This should convert all the previously non-date values into dates.
In C1 put
=if(B1="",A1,B1)
and formula copy that for the 4,000 rows.
Copy column C and Paste Special Values over itsself (column C)
You can then remove columns A & B but you will need to check those
dates that were initially converted, I suspect there will be errors
there.
If you can retrieve a copy of the original file of dates before any
conversion that would be excellant, set a column as Text format, paste
them in, and then do Text to Columns on the complete column specifying
the correct format.
Cound I guess that these were originally imported under Excel '97?
Of course, SAVE your work under a different filename before you make
ant changes.
martin0642 Wrote:
Hi all,
I have a large-ish spreadsheet (4000 entries) with a date of birth
column. For some reason when this file was ported from SPSS into Excel,
before being sent to me, the dates of birth got a bit screwed up.
ABout a third of them are in the format I want. UK format with
day-month-year. The rest are in US format with the month first. At the
moment I'm having to just go through them and type or copy hem into a
new column. Its essential I have the whole column in UK format.
Is there any formulas that can do this for me to save me typing it all
out?!! Any help would be MUCH appreciated!!!
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=483447