Populate a column with the dates of 1st, 3rd and 5th Mondays between two dates...

[raghuprabhu]

How to create a list of dates of every 1st, 3rd, 5th Monday each month between two dates?

I have in cell A2 (01 Jul 19) the first date and in B2 (30 Jun 20) the second date.

How do I populate column C with the dates of every 1st, 3rd, 5th Monday each month between 01 Jul 19 and 30 Jun 20.

Thank you

Kind regards

Raghu

 

[pgc01]

@ pgc01:

Your formula for cell C2 can return dates for the 2nd and 4th Mondays, too -- depending on the date in A2.

Here is a suggested modification:

=A2+MOD(2-WEEKDAY(A2),7*(1+(MONTH(A2)=MONTH(A2+MOD(2-WEEKDAY(A2),14)))))

Date in A2 assumed to be the 1st of a month.

Hi Tetra.

Thanks for looking into this. I had tried with several different months in 2019 and it worked OK.

Can it be, like footoo said, that you did not consider the start the first of the month? In that case my first formula would have to change.

From post #1 it seemed to me we are interested in full months. (?)

Let's see what the OP says.

 

[footoo]

There's a neat formula for the first Monday (assuming A2 is the 1st day of a month) from here : https://www.mrexcel.com/forum/excel-questions/468174-formula-find-next-monday.html

=CEILING(A2-2,7)+2

 

[pgc01]

There's a neat formula for the first Monday (assuming A2 is the 1st day of a month) from here : https://www.mrexcel.com/forum/excel-questions/468174-formula-find-next-monday.html

=CEILING(A2-2,7)+2

Nice formula.

Remark:
This formula will usually, most of the times, work OK.
This formula assumes that you use the 1900 date system. If you use the 1904 date system, for ex. MAC <=2008 or because you set the option, it will give you a wrong result.

 

[footoo]

Nice formula.

Remark:
This formula will usually, most of the times, work OK.
This formula assumes that you use the 1900 date system. If you use the 1904 date system, for ex. MAC <=2008 or because you set the option, it will give you a wrong result.

For the 1904 date system : =CEILING(A2-3,7)+3

 

[Scott R]

I like PGC's answer but here's an alternative...

D2: =WORKDAY.INTL(A2 - 1, 1, "0111111")
D3: =WORKDAY.INTL(D2, 2 - (DAY(D2 + 7) <= 7), "0111111")