CODEWITHSUNDEEP
excelvba
user3193297
user3193297

Reputation: 9

Changing the date format VBA

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?

Upvotes: 1

Views: 142

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

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!

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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.

Upvotes: 0

Related Questions