Reading dates

[SammyB]

On my computer, the default date format is yyyy-mm-dd; in other words, today's date in the bottom right corner of the screen is 2023-12-03. I have it set this way because I usually insert the date into files that I create & using this format makes sort go from oldest to newest.

Having said this, I am trying to read dates in text files that are formatted dd-mmm-yy, for example 30-NOV-23 for the end of last month. Is there some slick way to convert a string date such as 30-NOV-23 to a vba date?

I've also got text files that have the date formatted as mmmm d, yyyy. Could I mess with my regional settings in code just long enough to read the date & then reset them? Ideally, i guess that I'd like the reverse of the format function:

Code: Select all

Sub test()
    Dim s As String, d As String
    s = "30-NOV-23"
    d = ReadDate(s, "dd-mmm-yy")
    ' d on my computer would be 2023-11-30
End Sub

But, thinking of how to do that makes my head hurt.

[HansV]

You can use VBA's implicit type conversion if you declare d as a Date:

Code: Select all

Sub test()
    Dim s As String, d As Date
    s = "30-NOV-23"
    d = s
End Sub

Or, if you prefer to keep d as a String:

Code: Select all

Sub test()
    Dim s As String, d As String
    s = "30-NOV-23"
    d = Format(s, "yyyy-mm-dd")
 End Sub

[snb]

Code: Select all

Sub M_snb()
  MsgBox CDate("30-NOV-23")
End Sub

Please give an example of mmmm d, yyyy

Janvier, Januar, januari, january, Jänner, gennaio, enero or ? (regional format)

So basically:

Code: Select all

Sub M_snb()
  c00 = "gennaio 3, 2023"
  MsgBox DateSerial(Right(c00, 4), Application.Match(Split(c00)(0), [transpose(text(eomonth(1,row(1:12)-1),"[$-410]mmmm"))], 0), Val(Split(c00)(1)))
End Sub

[SammyB]

So far, with my regional settings
Short date: yyyy-MM-dd
Long date: dddd,MMMM d, yyyy
English(United States)
the only date that gets messed up is 30-NOV-23 (year & month are reversed):

cDate("30-NOV-23") is 2030-11-23.

Guess I can write code for that format.

[HansV]

Try this:

Code: Select all

Function ReadDate(s As String) As Date
    Dim p() As String
    p = Split(s, "-")
    ReadDate = DateSerial(p(2), (InStr("janfebmaraprmayjunjulaugsepoctnovdec", LCase(p(1))) + 2) \ 3, p(0))
End Function

Use as

d = ReadDate("30-NOV-23")

[SammyB]

That is close, but since the number for the year in "30-NOV-23" is 23, you need to compute the current century. Here's what I did:

Code: Select all

Private Function readDate(s As String, sFmt As String) As Date
    Dim d As Date, iCent As Long
    d = CDate(s)                ' so far the rest of the dates work
    If sFmt = "dd-mmm-yy" Then  ' month & year are reversed
        iCent = (Year(d) \ 1000) * 1000
        d = DateSerial(iCent + Day(d), Month(d), Year(d) - iCent)
    End If
    readDate = d
End Function

That's a little weird, but works so far.

[HansV]

On my PC, the version I posted returns a date in 2023:

S2477.png

It may be related to my Windows settings:

S2478.png

[SammyB]

My apologies, your code, of course, also worked and was simpler, so I'm using it. Thanks! And, thanks for the info on how Excel knows to turn 2 digit years into 4.

Code: Select all

Private Function ReadDate(s As String, sFmt As String) As Date
    Dim d As Date
    d = CDate(s)                ' so far the rest of the dates work
    If sFmt = "dd-mmm-yy" Then  ' month & year are reversed
        Dim p() As String
        p = Split(s, "-")
        d = DateSerial(p(2), (InStr("janfebmaraprmayjunjulaugsepoctnovdec", LCase(p(1))) + 2) \ 3, p(0))
    End If
    ReadDate = d
End Function

[snb]

Use Excel's builtin facilities:

Code: Select all

Sub M_snb()
  MsgBox ReadDate("30-NOV-23")
End Sub

Function ReadDate(s As String) As Date
  ReadDate = DateSerial(Split(s, "-")(2), Application.Match(Split(s, "-")(1), Application.GetCustomListContents(3), 0), Split(s, "-")(0))
End Function