How do I parse a nonstandard date format in Excel?

Question

I have a column of date-like string values in the format yyyy-mm-dd, such as 2011-Sep-13. I need to convert these to Excel date serial numbers so that I can use them in formulas.

DATEVALUE isn't able to recognize this format; I just get #VALUE! when I try. It also does not take a custom date format (unlike most programming languages).

How can I convert arbitrary date formats into Excel date values?

Answer 1

You'll need to do string parsing, then pass an appropriate formatted value into DATEVALUE – something like this:

=DATEVALUE(RIGHT(A1,2)&"-"&MID(A1,6,3)&"-"&LEFT(A1,4))

(edit: updated formula - tested and it works for me on one example - may need to refine depending on your input)

Answer 2

Note that month names are Windows Regional Options specific. So, if your Windows Regional Options are set to Ukrainian, then DATEVALUE would return #VALUE! for "07-Nov-2012", but would accept "07-Лис-2012".

Answer 3

Excels DateValue function is region dependent. If you parse a string like "04-11-2008" in Europe that would be parsed as 4th of November and in the US as 11th of April.

To get around this you can make use of the DateSerial function and do the parsing yourself. This can also be easily adapted to your own non standard date format.

' Region independent date parsing
' Expects a datetimestr in the format  "04-11-2008 21:39:39"
' The function VBA.DateTime.DateValue() mixes up the date and month in different regions
Function parseDateTime(dateTimeStr As String) As Date

    Dim parts As Variant
    parts = VBA.Strings.Split(dateTimeStr, " ")

    Dim datePart As String, timePart As String
    datePart = parts(0)
    timePart = parts(1)

    Dim dateParts As Variant, day As Integer, month As Integer, year As Integer
    dateParts = VBA.Strings.Split(datePart, "-")
    day = dateParts(0)
    month = dateParts(1)
    year = dateParts(2)

    Dim parsed_date As Date, parsed_time As Date
    parsed_date = VBA.DateTime.DateSerial(year, month, day)
    parsed_time = VBA.DateTime.TimeValue(timePart)

    parseDateTime = parsed_date + parsed_time
End Function

Answer 4

As others have noted, the Excel DATEVALUE function is region or locale dependent. For parsing yyyy-mm-dd you can use the DATE function with explicit parsing:

=DATE(LEFT(A1,4), MID(A1,6,2), RIGHT(A1,2))

For variable sized fields, such as 2021-9-9, it gets a little interesting (there are probably simpler ways):

=DATE(LEFT(A1,FIND("-",A1)-1), MID(A1,FIND("-",A1)+1,FIND("-",MID(A1,FIND("-",A1)+1,99))-1), MID(A1,FIND("-",A1)+FIND("-",MID(A1,FIND("-",A1)+1,99))+1,99))