How to change date from US to UK format

[martin0642]

Hi all,

I have a large-ish spreadsheet (4000 entries) with a date of birt
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 wit
day-month-year. The rest are in US format with the month first. At th
moment I'm having to just go through them and type or copy hem into
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 al
out?!! Any help would be MUCH appreciated!!

 

[Bryan Hessey]

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]

Thank you!!! It worked. You saved me a LOT of retyping!! Thanks again

 

[Dave Peterson]

Before you celebrate, you may want to format that column of dates to an
unambiguous format:

mmmm dd, yyyy

Then compare some of the birthdates to the original source.

If someone's birthdate were: 01/02/03, it may look like a valid date after you
imported it, but it might not be the real date (Jan 2, 2003, Feb 1, 1903)

 

[Bryan Hessey]

Thanks Dave, that's what I would have suspected (as per my comments)

If the incorrect dates can be identified (ie, original 'Dates', or al
have Month and Day < 13) then they can be cured with something like

=MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(A1)
or
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1)

but, identifying is the problem.

 

[Dave Peterson]

I find it much safer to reimport the data from the original source.

(Then I can sleep better at night <bg>.)