Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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
0
Cartillo
Asked:
Cartillo
  • 4
  • 2
  • 2
3 Solutions
 
plummetCommented:
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
 
gowflowCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
gowflowCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now