none
FORMAT function RRS feed

  • Question

  • Hi,

    I am trying to change the "mm/dd/yyyy" to "dd/mm/yyyy" using the below statement. 

    Range("c" & i).Value = Format(Range("c" & i).Value, "dd/mm/yyyy")

    However, the Format function is changing the date format only if the "mm" i.e month value is greater(>) than 12.

    Any solution for this?

    Regards
    Saby


    • Edited by Saby_84 Sunday, September 21, 2014 2:05 PM
    Sunday, September 21, 2014 2:05 PM

Answers

  • Try this - substitute the correct range. Note that we don't have to loop:

        With Range("C2:C50")
            .TextToColumns _
                Destination:=.Cells(1), _
                DataType:=xlDelimited, _
                Other:=False, _
                FieldInfo:=Array(1, xlMDYFormat)
            .NumberFormat = "dd/mm/yyyy"
        End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, September 21, 2014 2:22 PM

All replies

  • Try this - substitute the correct range. Note that we don't have to loop:

        With Range("C2:C50")
            .TextToColumns _
                Destination:=.Cells(1), _
                DataType:=xlDelimited, _
                Other:=False, _
                FieldInfo:=Array(1, xlMDYFormat)
            .NumberFormat = "dd/mm/yyyy"
        End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, September 21, 2014 2:22 PM
  • Perfect!!!

    Thanks Hans Vogelaar

    Regards
    Saby
    Sunday, September 21, 2014 3:44 PM