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

 

[footoo]

Enter in C2 : =$A$2+MOD(8-WEEKDAY($A$2,2),7)

Enter in C3 and drag down as far as required : =C2+7+(EOMONTH(C2,0)-DAY(C2)>6)*7

 

[pgc01]

Hi footoo

I tried and didn't get the right Mondays in August and September

01-07-2019
15-07-2019
29-07-2019
12-08-2019 second Monday
26-08-2019 fourth Monday
09-09-2019 second Monday
23-09-2019 fourth Monday

 

[footoo]

The results I get are :

1/7/2019

15/7/2019

29/7/2019

5/8/2019

19/8/2019

2/9/2019

16/9/2019

<tbody>
</tbody>

Edit : Posted the wrong formula. Correction :

=C2+7+(DAY(EOMONTH(C2,0)-DAY(C2))>6)*7

 

[pgc01]

Hi footoo

With your new formula seems OK now until October,

01-07-2019
15-07-2019
29-07-2019
05-08-2019
19-08-2019
02-09-2019
16-09-2019
30-09-2019
14-10-2019
28-10-2019

<colgroup><col></colgroup><tbody>
</tbody>

In October I got wrong values. Can you check?

 

[sandy666]

maybe try PowerQuery (Get&Transform)

is that what you want?

StartDate	EndDate		Date
01/07/2019​	30/06/2020​		01/07/2019​
			15/07/2019​
			29/07/2019​
			05/08/2019​
			19/08/2019​
			02/09/2019​
			16/09/2019​
			30/09/2019​
			07/10/2019​
			21/10/2019​
			04/11/2019​
			18/11/2019​
			02/12/2019​
			16/12/2019​
			30/12/2019​
			06/01/2020​
			20/01/2020​
			03/02/2020​
			17/02/2020​
			02/03/2020​
			16/03/2020​
			30/03/2020​
			06/04/2020​
			20/04/2020​
			04/05/2020​
			18/05/2020​
			01/06/2020​
			15/06/2020​
			29/06/2020​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Table.SelectColumns(Table.ExpandListColumn(Table.AddColumn(Table.AddColumn(Source, "Subtraction", each Duration.Days([EndDate] - [StartDate]), Int64.Type), "Date", each List.DateTimes([StartDate],[Subtraction]+1,#duration(1,0,0,0))), "Date"),{"Date"}),{{"Date", type date}}),
    Index = Table.AddIndexColumn(Type, "Index", 0, 1),
    Day = Table.AddColumn(Table.AddColumn(Index, "Month Name", each Date.MonthName([Date]), type text), "Day Name", each Date.DayOfWeekName([Date]), type text),
    Group = Table.Group(Day, {"Month Name", "Day Name"}, {{"CAR", each _, type table}}),
    Filter = Table.SelectRows(Group, each ([Day Name] = "Monday")),
    Counter = Table.AddColumn(Filter, "Index2", each Table.AddIndexColumn([CAR], "Index2", 1, 1)),
    Expand = Table.ExpandTableColumn(Table.RemoveColumns(Counter,{"CAR"}), "Index2", {"Date", "Index2"}, {"Date", "Index2.1"}),
    Filter1 = Table.SelectRows(Expand, each ([Index2.1] <> 2 and [Index2.1] <> 4)),
    Type1 = Table.TransformColumnTypes(Table.SelectColumns(Filter1,{"Date"}),{{"Date", type date}})
in
    Type1[/SIZE]

 

[pgc01]

Hi

Another option

In C2: =A2+MOD(2-WEEKDAY(A2),7)
In C3: =C2+7*(1+(MONTH(C2)=MONTH(C2+7)))

Copy down

 

[Tetra201]

@ 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)))))

 

[footoo]

Hi footoo

With your new formula seems OK now until October,

In October I got wrong values. Can you check?

Another typo. Should be : =C2+7+(DAY(EOMONTH(C2,0))-DAY(C2)>6)*7

 

[footoo]

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

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