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
LVL 22
Anne TroyEast Coast ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
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
Martin LissOlder than dirtCommented:
Run the 'Convert' macro in the attached workbook. Note that it also converts the Length column which you didn't mention in your question but you did in the workbook.

The code in the workbook looks like this:
Option Explicit

Sub Convert()
Dim intPos As Integer
Dim strTempDate As String
Dim strTempLength As String
Dim lngLastRow As Long
Dim lngRow As Long
'Dim strParts() As String

With ActiveSheet
    lngLastRow = .Range("A1048576").End(xlUp).Row
    For lngRow = 2 To lngLastRow
        intPos = InStrRev(.Cells(lngRow, 1), " ")
        strTempDate = Left$(.Cells(lngRow, 1), intPos - 1)
        .Cells(lngRow, 1) = Format(strTempDate, "mm-dd-yyyy hh:mm AM/PM")
        ' Modify Length
        strTempLength = .Cells(lngRow, 2).Text
        .Cells(lngRow, 2).NumberFormat = "General"
        .Cells(lngRow, 2) = DecimalHours(strTempLength)
    Next
End With
End Sub

Public Function DecimalHours(strLength As String) As String

Dim strParts() As String

strParts = Split(strLength, ":")
Select Case UBound(strParts)
    Case 0
        ' No data
        DecimalHours = strLength
        Exit Function
    Case 1
        ' Minutes and seconds
        DecimalHours = Format((strParts(0) / 60) + (strParts(0) / 3600), "0.##")
    Case 2
        ' Hours, Minutes and seconds
        DecimalHours = Format(strParts(0) + (strParts(1) / 60) + (strParts(2) / 3600), "#0.##")
    Case Else
        MsgBox "Unexpected time " & strLength
        DecimalHours = strLength
        Exit Function
       
End Select

End Function

Open in new window

Q-28687042.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anne TroyEast Coast ManagerAuthor Commented:
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 LissOlder than dirtCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.