Reading dates

SammyB
StarLounger
Posts: 75
Joined: 04 Mar 2010, 16:32

Reading dates

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

User avatar
HansV
Administrator
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reading dates

Post by 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
Best wishes,
Hans

snb
4StarLounger
Posts: 542
Joined: 14 Nov 2012, 16:06

Re: Reading dates

Post by 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
StarLounger
Posts: 75
Joined: 04 Mar 2010, 16:32

Re: Reading dates

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

User avatar
HansV
Administrator
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reading dates

Post by 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")
Best wishes,
Hans

SammyB
StarLounger
Posts: 75
Joined: 04 Mar 2010, 16:32

Re: Reading dates

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

User avatar
HansV
Administrator
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reading dates

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

SammyB
StarLounger
Posts: 75
Joined: 04 Mar 2010, 16:32

Re: Reading dates

Post by 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
4StarLounger
Posts: 542
Joined: 14 Nov 2012, 16:06

Re: Reading dates

Post by 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