Unexpected Date Results – Excel VBA – Day Month Year get mixed up

I took a workaround approach vs debugging to much and just scraped the month/year and then hard coded 1 for first day and used the function EoMonth to get the last day. This was being used to create a URL to query based on dates, the query will take a tiny bit longer and have extra data, but thats OK w/ me, I’d rather have reliability 100%.

here is the new code, which is also less complicated to read 🙂

Public Sub Get_Date()
 
DateHeader = "A"

Set rng = Application.ActiveSheet.Range(DateHeader & "1:" & DateHeader & Cells(Rows.Count, 1).End(xlUp).Offset(1).Row)

Dim OldestDate As Date, NewestDate As Date

OldestDate = Format(WorksheetFunction.min(rng), "mm/yyyy")
Debug.Print OldestDate
NewestDate = Format(WorksheetFunction.Max(rng), "mm/yyyy")
Debug.Print NewestDate

'20200908
'Sept 8, 2020

'OldestDate
aDate = Split(OldestDate, "/")
sYear = aDate(2)
sMonth = aDate(0)
If Len(sMonth) = 1 Then
 sMonth = "0" & sMonth
End If
sDay = "01"
OldestDateStr = sYear & sMonth & sDay
Debug.Print OldestDateStr

'NewestDate
aDate = Split(NewestDate, "/")
sYear = aDate(2)
sMonth = aDate(0)
If Len(sMonth) = 1 Then
 sMonth = "0" & sMonth
End If
sDay = Day(Application.WorksheetFunction.EoMonth(NewestDate , 0))
NewestDateStr = sYear & sMonth & sDay
Debug.Print NewestDateStr

End Sub

Debug: (Note I changed one date to Nov just for testing)

10/1/2020 
11/1/2020 
20201001
20201130
8/1/2020 
8/1/2020 
20200801
20200830