How to i convert a date entered as dd-mm-yy or dd-mm-yyyy to excel date

Question

I have a sheet with date column entered as dd/mm/yyyy eg 15/12/2014 However excel does not treat this as a date ,hence I cannot apply any date functions to this column .

Is there a way to convert this to excel date (mm/dd/yyyy) without using the tedious substring left$,mid$) way. Thanks in advance..

Answer 1

Excel will look into your regional settings and see how a date should be formatted and only if that matches, it will automatically format your cell as a date.

So if you have set your regional settings to use a date as dd-mm-yyyy (note the -), an output where / is used will be ignored.

Additionally, it may be the case that the cells have a ' in front of the date. This character will basically instruct the cell to always be a string. This is something excel could've done during an import.

First thing I would do is change the regional settings to american date so it has / and try to load the spreadsheet again. Selecting the entire column, chosing right-click->Format Cells, and set it to Date should now work.

Once it is succesfully converted to a date and you saved the sheet, you can change back your regional settings.

Answer 2

I devised following VBA code and run it ,but still looking for an easier way out:

Function ConvertddmmyyDate(instring As String) As Date
    On Error GoTo err
    Dim dateArray() As String
    dateArray = Split(instring, "/")
    Dim dt As Date
    Dim day As Integer
    Dim month As Integer
    Dim year As Integer
    day = Int(dateArray(0))
    month = Int(dateArray(1))
    year = Int(dateArray(2))
    If year < 100 Then 'For yy formats
        year = year + 2000
    End If
    dt = DateSerial(year, month, day)
    ConvertddmmyyDate = dt
    Exit Function
    err:
    ConvertddmmyyDate = "01/01/1900"
 End Function