Trying to understand the ArrangedDate Function in a lot of the posts

[Sherrie]

Hi all,

I was hoping someone could explain some bits of this code that I keep finding on here.

I've added some comments where I need help with understanding what their purpose is:


Function ArrangedDate(StrDateInput)
Dim StrFullDate As String
Dim StrYear As String
Dim StrMonthDay As String
Dim StrMonth As String
Dim StrDay As String
Dim StrDate As String
Dim RegX As Object

Set RegX = CreateObject("vbscript.regexp") (What is RegX for?)

If Not Left(StrDateInput, 2) = "10" And _ (What does this section do?)
Not Left(StrDateInput, 2) = "11" And _
Not Left(StrDateInput, 2) = "12" Then
StrDateInput = "0" & StrDateInput
End If

StrFullDate = Left(StrDateInput, 10)

If Right(StrFullDate, 1) = " " Then
StrFullDate = Left(StrDateInput, 9)
End If

StrYear = Right(StrFullDate, 4)
StrMonthDay = Replace(StrFullDate, "/" & StrYear, "")
StrMonth = Left(StrMonthDay, 2)
StrDay = Right(StrMonthDay, Len(StrMonthDay) - 3) (What is the -3?)
If Len(StrDay) = 1 Then
StrDay = "0" & StrDay
End If
StrDate = StrYear & "." & StrMonth & "." & StrDay
RegX.Pattern = "[\:\/\ ]"
RegX.IgnoreCase = True
RegX.Global = True

ArrangedDate = RegX.Replace(StrDate, "-")

ExitFunction:

Set RegX = Nothing

End Function


Thanks heaps for any help! :hi:

[JP2112]

(What Is RegX for?)

RegX is the object reference to a RegEx instance. RegEx is a parser for matching strings according to specific patterns.



(What does this section do?)


It adds a leading zero to the date if the month is 1-9 (January through September)



(What Is the -3?)


The code takes a string representation of a date, ex: "01/01/1980" and parses out the year, month and day from it. Pretty badly, I might add.

To get the year, the code takes the rightmost four characters:


Right("01/01/1980", 4) = "1980"

To get the month, first the code removes the last five characters:

Replace("01/01/1980", "/" & "1980", "") = "01/01"

Then it calculates the month by taking the two left characters:

Left("01/01", 2) = "01"

Finally, it calculates the day by taking from the right:

StrDay = Right("01/01", Len("01/01") - 3)

[Sherrie]

Thank you so much for clarifying that very explicitly! I greatly appreciate it!


The code takes a string representation of a date, ex: "01/01/1980" and parses out the year, month and day from it. Pretty badly, I might add.


How would you address the retrieval of the date in a better way?

[JP2112]

My version of the same code:

Function ArrangedDate(dateInput As String) As String

Dim RegX As Object
Dim checkDate As String

Set RegX = CreateObject("VBScript.RegExp")

If IsDate(dateInput) Then
checkDate = Format(dateInput, "yyyy.mm.dd")

With RegX
.Pattern = "[\:\/\ ]"
.IgnoreCase = True
.Global = True
End With

ArrangedDate = RegX.Replace(checkDate, "-")
End If
End Function