2

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..

3
  • I am in the UK and we format our dates as dd/mm/yyyy. I managed to format the cell as a date and excel was all happy with it. If excel isn't liking it for you you could try changing your location to the UK in windows, formatting the cell as a date, saving the file then change your location back again? EDIT: I've just tried it and you don't need to change your whole location just your date format. Apr 17, 2014 at 10:33
  • Can't you just select the column and change the cell format to Date?
    – Indrek
    Apr 17, 2014 at 10:40
  • @Richard for some reasons I don't have rights to change the regional setting in our computer.. mainly due to office policy ...
    – Thunder
    Apr 17, 2014 at 11:46

2 Answers 2

2

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.

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .