asked on

Uber Driver Workbook Part 1

Using VBA, I wish to convert the text values in column A to the (sample) date/time values in column G. (Keep in mind, I do not want to put the date/times in column G--they should remain in column A. I just can't sort them the way they are now.) Please be careful of the yellow cells, which do not have the :00 after the time because they occurred on the hour.

I will be taking care of some other things like removing hyperlinks, but I already have that code.

Thanks so much in advance!
SampleData.xlsx

als315

You can correct text value to be compatible with date-time functions in Excel:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"AM"," AM"),"PM"," PM")," EDT","")

Open in new window

and then convert it to date-time:

DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A35,"AM"," AM"),"PM"," PM")," EDT","")) + TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A35,"AM"," AM"),"PM"," PM")," EDT",""))

Open in new window

SampleData-1.xlsx

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anne Troy

ASKER

Martin: That's beautiful. I specifically asked for a VBA solution, and you did it. I was going to make Part B a second question, but thanks so much!!

Martin Liss

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015