Date sequencing formula - earliest of next Sunday and month end

[learjsy]

Hi,

I need to produce a list of dates from a starting point (say 31/12/20) which list, in order, every Sunday and every month-end date going forward ad infinitum. So, the output would be:

31/12/20
3/1/21
10/1/21
17/1/21
24/1/21
31/1/21
7/2/21
14/2/21
21/2/21
28/2/21
7/3/21
14/3/21
21/3/21
28/3/21
31/3/21
4/4/21

I have been playing around with permutations of min and max functions applied to the WORKDAY.INTL($A11,1,"1111110") and Eomonth function but, while the results looked promising at first, this was because for Jan and Feb, the month end also happens to be a Sunday; the formula doesn't work as I intend in March when the month end falls on a Weds.

Can someone please help tell me what the right formula should be?

Thanks!

 

[Dossfm0q]

what about

Public Sub XXXX()

StrtRw = 2
StrtRw = StrtRw - 1
StartDate = DateSerial(2020, 12, 31)
EndDate = DateSerial(2022, 12, 31)

N = 0

For MyDate = StartDate To EndDate
    
    If Weekday(MyDate, vbSunday) = 1 Or Day(DateSerial(Year(MyDate), Month(MyDate), Day(MyDate) + 1)) = 1 Then
    N = N + 1
    
    Cells(StrtRw + N, 1) = MyDate

End If

Next

End Sub

 

[learjsy]

Thank you so much and so sorry not to have been clearer from the start - I'm seeking a formula, not VBA.

So formula would reference the starting date cell

 

[GraH]

Since you asked for a formula, this might work.

Book1
	A	B	C
1	Startdate	31/12/2020
2	Enddate	31/12/2120	max date Excel can reference using a row value is "25/11/4770"
3			List of dates is "virtualized", see name manager AllDates
4
5		Extract EOM and Sundays
6		31/12/2020	and drag down
7		31/03/2021
8		30/04/2021
9		31/05/2021
10		30/06/2021
11		31/07/2021
12		31/08/2021
13		30/09/2021
14		30/11/2021
15		31/12/2021
Sheet1

Cell Formulas
Range	Formula
B6:B15	B6	=AGGREGATE(15,6,AllDates/(AllDates=EOMONTH(AllDates,0)+(WEEKDAY(AllDates,2)=7)),ROW(B1))

Named Ranges
Name	Refers To	Cells
Enddate	=Sheet1!$B$2	B6:B15
Startdate	=Sheet1!$B$1	B6:B15

AllDates: =ROW(INDIRECT(Startdate&":"&Enddate))

 

[FormR]

Hi, here's another formula option you could try.

Book10
	A
1	31/12/2020
2	03/01/2021
3	10/01/2021
4	17/01/2021
5	24/01/2021
6	31/01/2021
7	07/02/2021
8	14/02/2021
9	21/02/2021
10	28/02/2021
11	07/03/2021
12	14/03/2021
13	21/03/2021
14	28/03/2021
15	31/03/2021
16	04/04/2021
17	11/04/2021
18	18/04/2021
19	25/04/2021
20	30/04/2021
21	02/05/2021
22	09/05/2021
Sheet1

Cell Formulas
Range	Formula
A2:A22	A2	=MIN(EOMONTH($A1+1,0),WORKDAY.INTL($A1,1,"1111110"))

 

[learjsy]

=MIN(EOMONTH($A1+1,0),WORKDAY.INTL($A1,1,"1111110"))

Fantastic, I was close but yours does exactly what I need. Thank you.

Thanks GraH too for suggestion.

 

[GraH]

Fantastic, I was close but yours does exactly what I need. Thank you.

It is a nice one indeed, you should mark is as a solution.

Thanks GraH too for suggestion.

Glad to (try) to help out, but there was a severe mistake in my formula.
Just correcting for reference

Book1
	A	B	C
1	Startdate	31/12/2020
2	Enddate	31/12/2120	max date Excel can reference using a row value is "25/11/4770"
3			List of dates is "virtualized", see name manager AllDates
4
5		Extract EOM and Sundays
6		31/12/2020	Drag down
7		3/01/2021
8		10/01/2021
9		17/01/2021
10		24/01/2021
11		31/01/2021
12		7/02/2021
13		14/02/2021
14		21/02/2021
15		28/02/2021
Sheet1

Cell Formulas
Range	Formula
B6:B15	B6	=AGGREGATE(15,6,AllDates/((AllDates=EOMONTH(AllDates,0))+(WEEKDAY(AllDates,2)=7)>0),ROW(B1))

Named Ranges
Name	Refers To	Cells
Enddate	=Sheet1!$B$2	B6:B15
Startdate	=Sheet1!$B$1	B6:B15

 

[footoo]

Also : =MIN(EOMONTH(A1+1,0),A1+8-WEEKDAY(A1,1))

 

[Dossfm0q]

I like your simplicity, the ease of your answer and your advanced level of knowledge, I faced some complication resulting from the two parts sum 2 of the formula, but now it's okay

=IFERROR(TEXT(AGGREGATE(15,6,ROW(INDIRECT(DATE(2020,12,31)&":"&DATE(2021,12,31)))/(((WEEKDAY(ROW(INDIRECT(DATE(2020,12,31)&":"&DATE(2021,12,31))),17)=1)+(ROW(INDIRECT(DATE(2020,12,31)&":"&DATE(2021,12,31)))=EOMONTH(ROW(INDIRECT(DATE(2020,12,31)&":"&DATE(2021,12,31))),0)))-(--(((ROW(INDIRECT(DATE(2020,12,31)&":"&DATE(2021,12,31)))*(WEEKDAY(ROW(INDIRECT(DATE(2020,12,31)&":"&DATE(2021,12,31))),17)=1)))=EOMONTH(ROW(INDIRECT(DATE(2020,12,31)&":"&DATE(2021,12,31))),0)))),ROWS($A$1:A1)),"mm-dd-yy"),"")