Convert Data

Hi Experts,

I would like to request Experts help create a VBA code to convert all data starts with "24 to 30"  at Column A (Source sheet) into 00 to 06.

E.g. Data at cell A253 (24:19:02.00) become 00:19:02.00, data at Cell A300 (26:10:00.00) converted into 02:10:00.00, and data at cell A349 (29:52:21.00) become 05:52:21.00.

Hope Experts could help me create this feature. Attached the workbook for Experts perusal.

DataChange.xls
CartilloAsked:
Who is Participating?
 
plummetConnect With a Mentor Commented:
Hi Cartillo

I've created a little function to do this, which I have included in your module1. It looks like this:

Function TransformTime(sTimeIn As String) As String

    Dim sNewTime As String
    Dim i As Integer
    
    Select Case Val(Left$(sTimeIn, 2))
    Case 24 To 30
        i = Val(Left$(sTimeIn, 2))
        i = i - 24
        sNewTime = Format$(i, "00") & Mid$(sTimeIn, 3)
    Case Else
        sNewTime = sTimeIn
    End Select
    
    TransformTime = sNewTime
End Function

Open in new window


I hope it's what you were looking for.

Regards DataChange.xls
0
 
CartilloAuthor Commented:
Hi plummet,

Thanks for the code. Is that a possible to retain the data in the same column but make an alteration only at the start of the time?    
0
 
gowflowConnect With a Mentor Commented:
Hi Cartillo
Pls chk the attached file and activate the button Convert Time 24-30 to 00-06 and chk results.
gowflow
DataChange.xls
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
plummetCommented:
Hi Cartillo

Gowflow seems to have a good solution. I'm sorry I didn't get back to you earlier but I have been too busy to spend time on EE.

Good luck.
0
 
gowflowCommented:
plummet your a gentlmen ! your solution is good as well just move it Col A instead of Col D :)
gowflow
0
 
gowflowConnect With a Mentor Commented:
Cartillo
any chance to have tried any of the proposed solutions ?
gowflow
0
 
CartilloAuthor Commented:
hI,

Thanks for the help
0
 
gowflowCommented:
Your welcome tks for the grade
gowflow
0
All Courses

From novice to tech pro — start learning today.