Custom date formats

[Guest]

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

 

[Tom Ogilvy]

Even if you could get that as a standard format, I doubt it would alter
Excel's ability to recognize it as a date. Inf fact, in US English, in the
control panel under regional options, dates, that is the definition of the
long date format and it is available in Format Cells under data (2nd entry
for me). Excel still doesn't recognize is as a date when hand entered.

 

[Ron Rosenfeld]

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