none
Format Excel Date to MMDDYYYY RRS feed

  • Question

  • Hi All,

    I have few date fields in my excel file where date is entered in below format

    • June-4-2015
    • 6.04.15
    • 6/4/15

    How shall i format above dates like 06042015? I tried using excel date format and custom format by entering desired format to get date like mm/dd/yyyy but it do not work. Once i get the date in mm/dd/yyyy format, using below vba code, i change mm/dd/yyyy format to mmddyyyy.

    vba code activecell.numberformat= "mmddyyyy"

    Thanks,

    Zav

    Tuesday, February 23, 2016 5:00 PM

Answers

  • Select your cells with those date values and run this macro:

    Sub TestMacro()
        Dim rngC As Range
        Dim strV As String
        Dim V As Variant
        Dim dteV As Date
        Dim rngD As Range
        
        Set rngD = Selection
        
        For Each rngC In rngD
            dteV = 0
            strV = rngC.Value
            If InStr(1, strV, "-") Then
                V = Split(strV, "-")
                dteV = DateValue(V(0) & " " & V(1) & ", " & V(2))
            End If
            If InStr(1, strV, ".") Then
                V = Split(strV, ".")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            If InStr(1, strV, "/") Then
                V = Split(strV, "/")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            rngC.NumberFormat = "MMDDYYYY"
            If dteV <> 0 Then rngC.Value = dteV
        Next rngC
        
        rngD.EntireColumn.AutoFit
    End Sub

    • Marked as answer by zaveri cc Tuesday, February 23, 2016 9:14 PM
    Tuesday, February 23, 2016 8:45 PM
  • What would you want for that cell? This will ignore anything after the first space, so will give something - but the yyyy/mm/dd format was not in your initial list of value examples.....

    Sub TestMacro2()
        Dim rngC As Range
        Dim strV As String
        Dim V As Variant
        Dim dteV As Date
        Dim rngD As Range
        
        Set rngD = Selection
        
        For Each rngC In rngD
            dteV = 0
            
            strV = rngC.Value
            If InStr(1, strV, " ") Then
                strV = Split(strV, " ")(0)
            End If
            If InStr(1, strV, "-") Then
                V = Split(strV, "-")
                dteV = DateValue(V(0) & " " & V(1) & ", " & V(2))
            End If
            If InStr(1, strV, ".") Then
                V = Split(strV, ".")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            If InStr(1, strV, "/") Then
                V = Split(strV, "/")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            rngC.NumberFormat = "MMDDYYYY"
            If dteV <> 0 Then rngC.Value = dteV
        Next rngC
        
        rngD.EntireColumn.AutoFit
    End Sub


    Tuesday, February 23, 2016 10:46 PM

All replies

  • Select your cells with those date values and run this macro:

    Sub TestMacro()
        Dim rngC As Range
        Dim strV As String
        Dim V As Variant
        Dim dteV As Date
        Dim rngD As Range
        
        Set rngD = Selection
        
        For Each rngC In rngD
            dteV = 0
            strV = rngC.Value
            If InStr(1, strV, "-") Then
                V = Split(strV, "-")
                dteV = DateValue(V(0) & " " & V(1) & ", " & V(2))
            End If
            If InStr(1, strV, ".") Then
                V = Split(strV, ".")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            If InStr(1, strV, "/") Then
                V = Split(strV, "/")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            rngC.NumberFormat = "MMDDYYYY"
            If dteV <> 0 Then rngC.Value = dteV
        Next rngC
        
        rngD.EntireColumn.AutoFit
    End Sub

    • Marked as answer by zaveri cc Tuesday, February 23, 2016 9:14 PM
    Tuesday, February 23, 2016 8:45 PM
  • It works perfectly. Only throws error Run time error 13, Type mismatch for below date because of text at the end. Any way to solve that?

    2016/01/05 AW16

    Thanks,

    Zav

    Tuesday, February 23, 2016 9:14 PM
  • What would you want for that cell? This will ignore anything after the first space, so will give something - but the yyyy/mm/dd format was not in your initial list of value examples.....

    Sub TestMacro2()
        Dim rngC As Range
        Dim strV As String
        Dim V As Variant
        Dim dteV As Date
        Dim rngD As Range
        
        Set rngD = Selection
        
        For Each rngC In rngD
            dteV = 0
            
            strV = rngC.Value
            If InStr(1, strV, " ") Then
                strV = Split(strV, " ")(0)
            End If
            If InStr(1, strV, "-") Then
                V = Split(strV, "-")
                dteV = DateValue(V(0) & " " & V(1) & ", " & V(2))
            End If
            If InStr(1, strV, ".") Then
                V = Split(strV, ".")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            If InStr(1, strV, "/") Then
                V = Split(strV, "/")
                dteV = DateValue(V(0) & "/" & V(1) & "/" & V(2))
            End If
            rngC.NumberFormat = "MMDDYYYY"
            If dteV <> 0 Then rngC.Value = dteV
        Next rngC
        
        rngD.EntireColumn.AutoFit
    End Sub


    Tuesday, February 23, 2016 10:46 PM
  • Works great. Thanks.
    Wednesday, February 24, 2016 6:20 PM
  • Hi Bernie,

    I am getting error "Type mismatch" if date is

    11/27/2007, 06/04/2008

    What change i shall make in the code to remove second part of date?

    Thanks,

    Zav

    Monday, March 7, 2016 3:32 PM
  • Hi zaveri,

    For this new issue, I would suggest you post a new thread, and share us which code you used, and which line occurred this error.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 8, 2016 3:35 AM
  • After this:

          If InStr(1, strV, " ") Then
                strV = Split(strV, " ")(0)
            End If

    Add

          If InStr(1, strV, ",") Then
                strV = Split(strV, ",")(0)
            End If

    That will give you the 11/27/2007 from the string 11/27/2007, 06/04/2008
    Tuesday, March 8, 2016 1:42 PM