Convert text to date and time

[Bijaz]

Hi there,

I have a string of text with the following date/time format"
01/01/11 10:05.
How would I create a macro in which I could simply convert several hundred cells at once to an excel date time format?

Thanks!

 

[Boller]

Copy an empty cell.

Select the cells that need to be converted from Text to Date/Time.

Format them as dd/mm/yy hh:mm

PasteSpecial>Value>Add


If you need a macro, use the macro recorder to record the above steps.

 

[Rick Rothstein]

You don't need a macro to do that.... select your cells, click "Data/Text To Columns" of XL2003 or earlier or "Data/Data Tools/Text To Columns" on the Ribbon for XL2007 (sorry, don't have XL2010, so I'm not sure where it is there), then click the Finish button on the dialog box that appears.

 

[Rick Rothstein]

To follow up on my previous post though, if you do want to do this in a macro, simply create a range reference to your cells and do like this example...

Sub FixTimes()
Range("B2:B1000").TextToColumns Space:=False
End Sub

 

[Bijaz]

I'll give that shot and report back.

 

[Bijaz]

Worked!

 

[Rick Rothstein]

Worked!

Just out of curiosity, which one worked?

 

[Bijaz]

Rick,
Using text to column worked, but I couldn't create a macro that worked. I went through all the steps by selecting thte data d recording it, but it wouldn't work on other columns of data. Any ideas about what I'm doing wrong?

 

[Rick Rothstein]

I would have thought the macro I posted would have worked for you then as long as you changed the range from the example B2:B1000 to whatever range you actually wanted. Here, try this variation... select the column of cells that you want convert and then run this macro...

Sub FixTimes()
    Selection.TextToColumns Space:=False
End Sub

I didn't test it, but it should work. Let me know.

 

[Bijaz]

I'll try when I get back to work in the am. Thanks again for your help!