Changing Time Format with a macro

[ceefor]

Hello everyone,

I am well aware of how to change the format of a time in a cell, however Excel doesn't recognize the following format: "05:23pm" as an actual time format, therefore changing the format is futile.

I imagine that I need to add a space between the ":23" and "PM" for it to recognize and allow me to manipulate which time format I want.

I thought of recording a macro that would replace "xpm" with "x PM", however I am not sure what I would use for the wildcard.

Any help here is appreciated as always.

Thank you,
ceefor

 

[CWatts]

If you do a find/replace and replace "pm" with " pm" (without the quotes) you'll be fine without a wildcard.

Here's a simple vba macro made with the recorder that will do the trick for you:

Sub AMPMChange()
    Cells.Replace What:="pm", Replacement:=" pm", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="am", Replacement:=" am", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[ceefor]

Ah, that easy eh?! LOL

Thank you very much for the help!