Sharepoint date(text) to date format

[Kiran Kumar]

Hi there,


I know it is silly, but I actually stuck in getting the solution.


I have a share point dowloaded data in A to H columns. My G and H columns shows me the dates like below:


G Column:

08/21/2012 08:20:25 AM

08/20/2012 02:20:10 PM


H Column

08/21/2012

08/20/2012


Now, I want G column be dd/mm/yyyy hh:mm:ss AM/PM format and H column as dd/mm/yyyy format.


I have tried changing the formats through format cells, but the cells are not recognised as dates only.


Please someone have a look onto this.


Rgds,

KK

 

[Debraj]

Hi KK,


Is it possible to add helper Column for this JOB ?

Select Col("G:G")..

Data >> Text To Column >> Fixed Width. Select both column @ 10 and 21 >> In Next screen

Change 1st One (with Date) from General to Date >> Date(MDY)

Change destination to some Blank Column.. For Example 'Column J'

Click Finish, and EXCEL will do the rest for you..


Hope now you can manage Col("H:H")


Regards,

Deb

 

[Weow]

I've been doing a lot of work with sharepoint data in excel. Its annoying that the dates in sharepoint aren't recognized as dates by excel. But I've found you can generally just use excel's DATEVALUE function. so DATEVALUE(your_sharepoint_date) will change the text only 08/20/2012 02:20:10 PM into an excel serial number which you can manipulate using number formats.