Formula breakdown please

[scottylad2]

This is my first post of the 20's, I had lost all my login details many moons ago but now I've found them again I had a reply from the Excel guru Barry Houdini a good few years back to a question. This is an adapted version of the reply, I was looking for an easy way to find the First Monday of the month.

Where the date in A2 is 01/01/2020 the formula reads

=A2+7-(Weekday A2+5)
This does indeed return the first Monday of the month. I've adapted the formula to read =DATE(2020,ROWS(C$2:C2),1)+7-WEEKDAY(A2+5) and I can drag through as many colum as a want to get the first Monday of the months goinf forward, dragging as many as i choose.

My problem is I don't actually understand what the formula is doing and how I can adapt it to have the first any weekday I chhose.

I hope this makes sense

 

[Scott T]

Weekday returns the day of the week as a number. There are different numbering to choose from but in this case you are using 1=Sun tp 7=Sat. So the formula takes the date and adds 7 days then subtracts the week number of the date plus 5 days.
In the case of 1/1/20 when you add 7 days you get 1/8/20. the Weekday of 1/6/20(1/1/20+5days) is 2 (that is Monday) this gives 1/8/20-2 days =1/6/20. It does the same thing for the others months.