Finding first Monday in any year with VBA... With a caveat

[RockandGrohl]

Hello everyone, happy new year.

I'd like to have a report that generates all Week commencing dates vertically, which I'm fine with doing, but it needs to start on the week that contains the first days in January.
For example, in 2021 the first Monday was the 4th Jan, but for this purpose, the initial week commencing date is actually the 28th December 2019 as it contains days for January 1, 2 and 3.

Likewise, for 2022 it's December 27th as the Saturday & Sunday are January 1st and 2nd.

So does anyone have some VBA that can find the first week which has the first days of January in?

Thank you!

 

[edmitchell]

Hi

assuming I've understood correctly, and that a formula would work (rather than VBA), how about

=DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-2) - where A2 holds the relevant year (e.g. 2021, etc)

Hope it helps