Changing the date format VBA

Question

I've got a date column that look like this:

04-03-2013 01:57:32 PM IST

I am trying to change it to look like this:

04/03/2013 13:57:32

my code:

Columns("D:E").Select 
Selection.Replace What:=" IST", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Selection.Replace What:=" IDT", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Selection.Replace What:="-", Replacement:="/", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

the problome is that it change it to look like that:

04-03-2013 01:57:32 PM

and only when I press manually ENTER after "PM" it changes to:

04/03/2013 13:57:32

How can i do it automatically?

Jean-François Corbett · Accepted Answer · 2014-01-14 13:17:42Z

2

I think you just need to specify the desired number format:

Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss" 
' or mm/dd/yyyy, not sure which you want!

edited Jan 14, 2014 at 13:17

answered Jan 14, 2014 at 9:17

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2014-01-14 11:56:45Z

0

If you want to convert the original dates to "real" Excel dates that are formatted as above, then try, in a regular module:

Option Explicit
Sub ChangeDateTimeFormat()
    Dim Rng As Range
    Dim C As Range
Set Rng = Range("D:E").SpecialCells(xlCellTypeConstants)
For Each C In Rng
    With C
        If Right(.Text, 4) = " IDT" Or Right(.Text, 4) = " IST" Then
            .Value = DateValue(Left(.Text, Len(.Text) - 4)) + TimeValue(Left(.Text, Len(.Text) - 4))
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End If
    End With
Next C
End Sub

This was only tested with dates in MDY format. If your dates are in DMY format, and this doesn't work, I will test further.

answered Jan 14, 2014 at 11:56

Ron Rosenfeld

62.1k7 gold badges35 silver badges71 bronze badges

Collectives™ on Stack Overflow

Changing the date format VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related