On Mon, 7 Feb 2005 08:11:05 -0800, Bhupinder Rayat
wrote:

Is there any way I can take the custom date format "dddd dd mmmm yyyy" and
use it as a compulsory date format (i.e so it will be an option it the 'date'
option in format cells, as opposed to a 'custom' option).

The reason why I am asking is the I have a process where I download a table
(coded in HTML) into excel, using the Workbooks.Open Filename command and
one of the rows in the table will have a date inserted in the format "dddd
dd mmmm yyyy". The trouble is that excel does not recognise this a date when
the page is downloaded into excel.

I have tried various ways to get around this but to no avail.

Can anyone help?

Bhupinder Rayat

Formatting only changes how dates are displayed. It does NOT change how date
information is parsed.

Formatting will NOT enable Excel to recognize that string as a date.

2 solutions:

1. Data/Text to Columns
Fixed Width <Next
DEselect the column breaks except for the one after the weekday
<Next
Column Data Format
<Skip (column 1)
Date DMY
<Finish

2. If your dates are in column A1:An, you can enter a formula in some column:

=DATEVALUE(MID(A1,FIND(" ",A1)+1,255))

and copy/drag down as needed.

Depending on what, exactly, is in the cell, you may need to modify that
formula:

=DATEVALUE(MID(TRIM(SUBSTITUTE(A1,CHAR(160),"")),
FIND(" ",TRIM(SUBSTITUTE(A1,CHAR(160),"")))+1,255))


--ron