Help! Search & Replacing time formats

[glenster]

Hi

I have a whole spreadsheet full of fields similiar to the following

10:00AM 12:15PM 2:25PM 4:40PM 7:00PM 9:20PM 10:00PM
12:20PM 2:25PM 4:30PM 7:15PM
10:00AM 12:30PM 2:45PM 9:15PM
10:40AM 1:10PM 5:00PM 7:20PM 9:35PM

etc.

I need to convert all times to 24 hour, and drop the AM & PM.

The later is easy, but how do I do a quick Search & Replace without
incorrectly converting 10:**AM to 22:** etc.

Any tips would be appreciated.

 

[Bernie Deitrick]

glenster,

If they are really times and not just strings, then you can simply
format them for military time.

If they are strings and not times (the formatting won't change the
displayed value if they are strings), you will need to replace

PM

with

:00 PM

(Same for AM --> :00 AM)

Then enter a 1 into a blank cell, copy it, select all your time cells,
right-click, choose pastespecial... values, multiply.

Then format for military time, and you're done.

HTH,
Bernie

 

[glenster]

Gee thanks for your reply Bernie - you're the man!

BUT, not quite there yet!

Unfortunately your tip only worked for fields with only one time in it.
Most of my row fields have multiple times in them seperated by two
spaces:

eg:

10:00AM 12:15PM 2:25PM 4:40PM 7:00PM

Your tip worked up until the last step of formating to military time,
but failed to do so as I think perhaps it didn't recognise the above as
seperate times in the one field?

 

[Dave Peterson]

I thought your time values were all in separate cells, too.

But if you select your cells to modify, then run this macro, it looked like it
worked ok for me:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myStr As String
Dim myTime As Date

Set myRng = Selection
For Each myCell In myRng.Cells
myStr = ""
iCtr = 1
Do
If UCase(Mid(myCell.Value, iCtr, 7)) Like "##:##[AP]M" Then
myTime = TimeValue(Mid(myCell.Value, iCtr, 7))
myStr = myStr & Format(myTime, "hh:mm")
iCtr = iCtr + 7
ElseIf UCase(Mid(myCell.Value, iCtr, 6)) Like "#:##[AP]M" Then
myTime = TimeValue(Mid(myCell.Value, iCtr, 6))
myStr = myStr & Format(myTime, "hh:mm")
iCtr = iCtr + 6
Else
myStr = myStr & Mid(myCell.Value, iCtr, 1)
iCtr = iCtr + 1
End If
If iCtr > Len(myCell.Value) Then
Exit Do
End If
Loop
myCell.Value = myStr
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[glenster]

Wow Dave - works like a dream!

Thanks to both Dave and Bernie for your assistance. Knowledge is Power!