Converting Text to Date

[joeKen]

Hi,
I have some data generated by another app. I'm writing a macro to convert my table data into a pivot table. However I've found the date information has come across as a text format.

I tried changing the format to be a date format, but I think I need to clear out the data change the cell format to Date and then copy the xlValue back in. This seems a bit cumbersome!!

Am I on the right track here??

Cheers, Joe

 

[xlbo]

kinda. You shouldn't need to clear the data out but that depends on teh format of the data. You are right in that you cvannot just firmat the text to convert it though.

If your dates are in date format (ie something like dd/mm/yyyy) but are just being stored as text then you just need to multiply them by 1 and re-format. Otherwise, you will need to re-create the dates in a format that excel can recognise before converting:

dim lRow as long
lRow = cells(65536,1).end(xlup).row ' assumes dates in column A (column 1)
Range("IV1").value = 1
range("IV1").copy
with Range("A2:A" & lRow)
    .pastespecial paste:=xlPasteValues, operation:=xlPasteSpecialOperationMultiply
application.cutcopymode = false
    .numberformat="dd/mm/yyyy"
end with

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi joeKen,

If your dates are in a recognised date format, you should be able to convert them on import. How are you getting the data into Excel?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[KenWright]

If your date text is really in date format, eg 01/12/2004 etc, then another option is to select all your dates, do Data / Text To Columns / Fixed Width, hit Next, choose the Date Option from the radio buttons and select the one that represents your data.

Lots of ways to skin this cat

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[joeKen]

Thanks for all the advice. I'm checking some stuff out now.

FYI, the information is download from a web site. To make things more fun for me, the date format is in US MDY and being British I need DMY.- call me fussy Some of the stuff I've tried gives some bizarre results so I'll let you know how I get on.

Many thanks,

JoeKen

 

[KenWright]

Two other things to consider given where your data is coming from:-

1) You can try right clicking on your webpage and seeing if you have the option 'Analyse Data with Microsoft Excel'

2) You can try the Data / Import External data / New Web Query to get the data directly from the site.

The US date format makes no odds when following the Data ? text To Columns option I outlined - You just choose the appropriate format.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------