Public Sub Form_Open()
Dim iLoop, iMonth As Integer
Dim iYear As Integer = 2022
Dim dDate As Date
For iLoop = 2 To 13
If iLoop = 13 Then
iYear += 1
iMonth = 1
Else
iMonth = iLoop
End If
dDate = Date(iYear, iMonth, 1) - 1 ' ' <<<< this does work
Print Format(dDate, "dd/mm/yyyy") & " -- " & Format(dDate, "dddd d mmmm yyyy")
Next
End
result :31/01/2022 -- Monday 31 January 2022
28/02/2022 -- Monday 28 February 2022
31/03/2022 -- Thursday 31 March 2022
30/04/2022 -- Saturday 30 April 2022
31/05/2022 -- Tuesday 31 May 2022
30/06/2022 -- Thursday 30 June 2022
31/07/2022 -- Sunday 31 July 2022
31/08/2022 -- Wednesday 31 August 2022
30/09/2022 -- Friday 30 September 2022
31/10/2022 -- Monday 31 October 2022
30/11/2022 -- Wednesday 30 November 2022
31/12/2022 -- Saturday 31 December 2022