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!!!


--
martin0642
------------------------------------------------------------------------
martin0642's Profile: http://www.excelforum.com/member.php...o&userid=27589
View this thread: http://www.excelforum.com/showthread...hreadid=483447

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

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


--
martin0642
------------------------------------------------------------------------
martin0642's Profile: http://www.excelforum.com/member.php...o&userid=27589
View this thread: http://www.excelforum.com/showthread...hreadid=483447

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 we 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)

martin0642 wrote:

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

--
martin0642
------------------------------------------------------------------------
martin0642's Profile: http://www.excelforum.com/member.php...o&userid=27589
View this thread: http://www.excelforum.com/showthread...hreadid=483447

--

Dave Peterson

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

If the incorrect dates can be identified (ie, original 'Dates', or all
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 Wrote:
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 we 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)

martin0642 wrote:

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

--
martin0642

------------------------------------------------------------------------
martin0642's Profile:
http://www.excelforum.com/member.php...o&userid=27589
View this thread:
http://www.excelforum.com/showthread...hreadid=483447

--

Dave Peterson


--
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

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

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

Bryan Hessey wrote:

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

If the incorrect dates can be identified (ie, original 'Dates', or all
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 Wrote:
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 we 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)

martin0642 wrote:

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

--
martin0642

------------------------------------------------------------------------
martin0642's Profile:
http://www.excelforum.com/member.php...o&userid=27589
View this thread:
http://www.excelforum.com/showthread...hreadid=483447

--

Dave Peterson

--
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

--

Dave Peterson