VBA Replace Values in selection

[sachavez]

Excel 2007

Need some help with a quick macro that will all the user to change the values in selection:

04110508 ---> 04/11/2013 05:08
04141954 ---> 04/14/2013 19:54

Note that the original format is MMDDHHMM; need the macro to format as MM/DD/YYYY HH:MM

Thanks, in advance.

 

[Andrew Poulsom]

Is the original value a serial date and time? If so you just need to change the number format.

 

[BobUmlas]

Sub fixit()
For Each thing In Selection
thing.NumberFormat = "m/d/yyyy hh:mm"
thing.Value = DateValue(Left(thing.Value, 2) & "/" & Mid(thing.Value, 3, 2) & "/2013") + _
TimeValue(Mid(thing.Value, 5, 2) & ":" & Right(thing.Value, 2))
Next
End Sub

I'm leading an online 2-evening/3-hr each class in VBA. Details here:

The class is held June 4 & 6 from 8-11PM est.
If you're interested in taking a live, online VBA class, check this out:
go to IIL: Corporate training solutions and consulting services
on the left side, click the link "Virtual Classroom"
on the right side, click "Virtual Classroom Schedule"
click the MIDDLE Dropdown, scroll to Microsoft Excel Visual Basic for Applications
click "See your selection"
click Course Outline (or register, etc)

Excel 2007

Need some help with a quick macro that will all the user to change the values in selection:

04110508 ---> 04/11/2013 05:08
04141954 ---> 04/14/2013 19:54

Note that the original format is MMDDHHMM; need the macro to format as MM/DD/YYYY HH:MM

Thanks, in advance.

 

[par60056]

There are a number of approaches for this.

Add a column with the formula "=DATE(YEAR(NOW()),mid(A1,1,2),mid(A1,3,2))+TIME(MID(A1,5,2),MID(A1,7,2),0)"

If you want to convert them in place define this macro and it will convert anything in the selection range when run.

Sub ctime()
Dim theCell As Range


For Each theCell In Selection
    If Len(theCell.Value) = 8 Then
        theCell.Value = CDate(Mid(theCell.Value, 1, 2) & "/" & Mid(theCell.Value, 3, 2) & "/" & Year(Date) & " " & Mid(theCell.Value, 5, 2) & ":" & Mid(theCell.Value, 7, 2))
    End If
Next
End Sub

 

[sachavez]

Thanks, Bob!

I just realized, when I paste the 04110508 into Excel, Excel drops the leading zero. The code works like a champ when the months are 10, 11 or 12. Any ideas for a workaround when the months are 01-09?

Steve

 

[Rick Rothstein]

You wanted to physically change those values to what you showed within the same cell, correct? If so, give this macro a try...

Sub MakeIntoDateTimesForCurrentYear()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = Replace(Format(Cell.Value, "0\/00\/\# 00\:00"), "#", Year(Now))
  Next
  Selection.NumberFormat = "mm/dd/yyyy hh:mm"
End Sub

 

[par60056]

Try formatting the column as TEXT before pasting into it. This will keep it from converting them to numbers

 

[sachavez]

Thanks Par60056!


Per my previous comments regarding the leading zero, is there a work-around to your code? When I paste the numbers in excel, the cell value = 7 (04150934 ---> the lead zero falls off when I paste).

There are a number of approaches for this.

Add a column with the formula "=DATE(YEAR(NOW()),mid(A1,1,2),mid(A1,3,2))+TIME(MID(A1,5,2),MID(A1,7,2),0)"

If you want to convert them in place define this macro and it will convert anything in the selection range when run.

Sub ctime()
Dim theCell As Range


For Each theCell In Selection
    If Len(theCell.Value) = 8 Then
        theCell.Value = CDate(Mid(theCell.Value, 1, 2) & "/" & Mid(theCell.Value, 3, 2) & "/" & Year(Date) & " " & Mid(theCell.Value, 5, 2) & ":" & Mid(theCell.Value, 7, 2))
    End If
Next
End Sub

 

[Rick Rothstein]

Per my previous comments regarding the leading zero, is there a work-around to your code? When I paste the numbers in excel, the cell value = 7 (04150934 ---> the lead zero falls off when I paste).

Just wanted to point out that the code I posted does not care whether the leading zero is there or not.

 

[sachavez]

Bingo! Rick, excellent solution. Works like a champ. Thank you very much!!!

Steve

You wanted to physically change those values to what you showed within the same cell, correct? If so, give this macro a try...

Sub MakeIntoDateTimesForCurrentYear()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = Replace(Format(Cell.Value, "0\/00\/\# 00\:00"), "#", Year(Now))
  Next
  Selection.NumberFormat = "mm/dd/yyyy hh:mm"
End Sub