date recognition problem

C

Chandler

Hi.
My problem is that I import a table with a number of columns (using "New web
query"), one of which (D) is a column of dates in the form "08 July 2007"
(two spaces between the number and month as it happens). In Import External
Data - Options I am forced to select Disable Date Recognition due to the
content of another of the columns which is misread otherwise. Once the table
is imported into the worksheet I need to get Excel (2003) to recognise the
date column (i.e. D) as dates, which it refuses to do even if I select the
column and Format - Cells... - Date it, or correct the two spaces into the
next column using

=LEFT(D1,2)&RIGHT(D1,LEN(D1)-3)

and then formatting that column using Format - Cell - Date.

Excel will recognise the individual D cells as dates if I click the cursor
in each one first but this is not a feasible solution since the column of
dates is very long.

I'd be grateful if anyone knows a solution. My suspicion is that there
isn't one.

Regards

Chandler
 
M

Max

Try Data > Text to Columns. There's a step 3 in the wizard where you can
configure it to help Excel recognize the source date format.

Select the col of "dates", click Data > Text to Columns. Click Next > Next.
In step 3, under Column data format check "Date", then select: DMY from the
droplist. Click Finish.
 
S

Sandy Mann

See if this works for you.

I assume that the problem is that the column is formatted as Text or at
least the data within it is. I formatted the column as text and then
entered your text date. Next I re-formatted the column as Date the
highlighted the column and selected Edit > Replace > Find what: (entered two
spaces) > Replace with (entered one space) > Replace all.

All the dates in the column then changed

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Ragdyer

TTC (Text To Columns) should convert that data to XL recognizable dates
quite easily, just by opening and closing TTC.

Select the column of imported dates, then click:
<Data> <Text To Columns> <Finish>

And you should now have a column of XL "legal" dates.
 
C

Chandler

Thanks to all. Very helpful

Chandler

Ragdyer said:
TTC (Text To Columns) should convert that data to XL recognizable dates
quite easily, just by opening and closing TTC.

Select the column of imported dates, then click:
<Data> <Text To Columns> <Finish>

And you should now have a column of XL "legal" dates.
Click to expand...
 
Top