VBA - Change date in text-format to date-format RRS feed

  • Question

  • Hi,

    I have the following date in general text-format:

    This text is copied from another file into this cell. However, it somehow shows it as a text instead of a date (some dates have this problem, others don't). 

    How can I, using VBA, edit this text into a 'real' date? I tried to use CDate, but that one didn't work.

    Thanks in advance.


    Monday, March 26, 2018 8:09 AM


  • Hi,

    You can do it with using "CDate" function and "NumberFormatLocal" property.

    Code in [Format Cells] button:
    Private Sub btn_FormatCells_Click()
        Dim myRow As Integer
        For myRow = 1 To 7
            Cells(myRow, 2).Value = CDate("26/09/2017")
        ' ---
        Cells(1, 2).NumberFormatLocal = "mm/dd/yy"
        Cells(2, 2).NumberFormatLocal = "dd/mm/yyyy"
        Cells(3, 2).NumberFormatLocal = "d/m/yy"
        Cells(4, 2).NumberFormatLocal = "yyyy/m/d (ddd)"
        Cells(5, 2).NumberFormatLocal = "yyyy/mm/dd (dddd)"
        Cells(6, 2).NumberFormatLocal = "mmm/dd/yyyy"
        Cells(7, 2).NumberFormatLocal = "mmmm/dd/yyyy"
    End Sub

    Ashidacchi --

    • Marked as answer by ganeshgebhard Monday, March 26, 2018 3:57 PM
    Monday, March 26, 2018 9:54 AM