date recognition problem

[Guest]

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

 

[Guest]

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.

 

[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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

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

 

[Guest]

Thanks to all. Very helpful

Chandler

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.