Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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