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 :

[TABLE="width: 121"]
<tbody>[TR]
[TD]1/7/2019[/TD]
[/TR]
[TR]
[TD]15/7/2019[/TD]
[/TR]
[TR]
[TD]29/7/2019[/TD]
[/TR]
[TR]
[TD]5/8/2019[/TD]
[/TR]
[TR]
[TD]19/8/2019[/TD]
[/TR]
[TR]
[TD]2/9/2019[/TD]
[/TR]
[TR]
[TD]16/9/2019[/TD]
[/TR]
</tbody>[/TABLE]

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,

<colgroup><col></colgroup><tbody>
[TD="align: right"]01-07-2019
[/TD]

[TD="align: right"]15-07-2019
[/TD]

[TD="align: right"]29-07-2019
[/TD]

[TD="align: right"]05-08-2019
[/TD]

[TD="align: right"]19-08-2019
[/TD]

[TD="align: right"]02-09-2019
[/TD]

[TD="align: right"]16-09-2019
[/TD]

[TD="align: right"]30-09-2019
[/TD]

[TD="align: right"]14-10-2019
[/TD]

[TD="align: right"]28-10-2019
[/TD]

</tbody>

In October I got wrong values. Can you check?

 

[sandy666]

maybe try PowerQuery (Get&Transform)

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]StartDate[/td][td=bgcolor:#5B9BD5]EndDate[/td][td][/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/07/2019​

[/td][td=bgcolor:#DDEBF7]

30/06/2020​

[/td][td][/td][td=bgcolor:#E2EFDA]

01/07/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

15/07/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

29/07/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

05/08/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

19/08/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

02/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

16/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

30/09/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

07/10/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

21/10/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

04/11/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

18/11/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

02/12/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

16/12/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

30/12/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

06/01/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

20/01/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

03/02/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

17/02/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

02/03/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

16/03/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

30/03/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

06/04/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

20/04/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

04/05/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

18/05/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

01/06/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

15/06/2020​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

29/06/2020​

[/td][/tr]
[/table]

[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.