How to make Excel treat the dates AS DATES which are taken from a Web Page ?

[sorabh]

Hi guyz,
i have a web page which has a column in which dates are given in the
following format dd/mm/yy hh:mm:ss which is a perfectly acceptable
format for excel, then i opened this web page through excel so as to
parse the data into different columns, but when i proceeded to perform
some operations like month() weeknum() etc on the date column, excel
returned errors of !value, ie excel is still treating that perfect date
column as text, and no.... changing the format of the cells didnt help,

so people plz advise as to how to make excel treat that column as a
proper date column so as to allow performing some date operations.
regards
sorabh

PS: i have attached a file which contains the data which was taken from
the web page as u can see the weeknum doesnt work
even if the format is changed to date!

Attachment filename: dates 4m webpage.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=505937

 

[kkknie]

Your dates in column A have two trailing spaces which excel is chokin
on. Remove these and all should work. Here's one way.

Insert a column between A and B, use this function for all row
=Trim(A1), then do a copy of column B, Paste Special | Values. The
delete column A.

 

[Frank Kabel]

Hi
try the following
- apply a date format for all your dates
- select an empty cell
- copy this cell
- select your date values
- goto 'Edit - Paste Special' and choose the action 'Add'

this should convert your dates which are probably currently stored as
text values to real dates

 

[sorabh]

Hi k,
i removed the trailing spaces but it still dint work infact if u make
copy of =trim(a3) and paste is somewhere and check it , it still ha
two blank sapces !!. this dint work. but good observation !!.
You have my sample file if you can work this out would u please uploa
a copy of it,
thanks for the effort

Hi Frank,
unfortunately your method aint wrkd either, you too can take the sampl
file and if it works for you please upload the working version.
thanks
.....
anybody with more ideas please pour in ( this is is an officia
presentation that i need to prepare, !!

Attachment filename: trim dint work.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=50602

 

[Frank Kabel]

Hi
try the following:
- select your column A
- goto 'Data - Text in columns'
- choose 'fixed with' in the first tab of this wizard
- in the next tab move the vertical lines so that they end right after
your date/time value (this will skip the two trailing Spaces)
- choose the type 'date' and select the appropiate date format ('MDY'
in your case)
- finish the wizard

This works for me. If you need a copy provide your email address

 

[sorabh]

Hi frank
thanx a ton man, brilliant job done, it worked perfectly.
great
thanks for takin time out to reply to this one.
you save me.
regards
sorab