# Date sequencing formula - earliest of next Sunday and month end

#### learjsy

##### New Member
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!

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Dossfm0q

##### Banned User
what about
VBA Code:
``````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

##### New Member
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

##### Well-known Member
Since you asked for a formula, this might work.
Book1
ABC
1Startdate31/12/2020
2Enddate31/12/2120max date Excel can reference using a row value is "25/11/4770"
3List of dates is "virtualized", see name manager AllDates
4
5Extract EOM and Sundays
631/12/2020and drag down
731/03/2021
830/04/2021
931/05/2021
1030/06/2021
1131/07/2021
1231/08/2021
1330/09/2021
1430/11/2021
1531/12/2021
Sheet1
Cell Formulas
RangeFormula
B6:B15B6=AGGREGATE(15,6,AllDates/(AllDates=EOMONTH(AllDates,0)+(WEEKDAY(AllDates,2)=7)),ROW(B1))
Named Ranges
NameRefers ToCells
Enddate=Sheet1!\$B\$2B6:B15
Startdate=Sheet1!\$B\$1B6:B15

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

#### learjsy

##### New Member
 =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

##### Well-known Member

ADVERTISEMENT

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
ABC
1Startdate31/12/2020
2Enddate31/12/2120max date Excel can reference using a row value is "25/11/4770"
3List of dates is "virtualized", see name manager AllDates
4
5Extract EOM and Sundays
631/12/2020Drag down
73/01/2021
810/01/2021
917/01/2021
1024/01/2021
1131/01/2021
127/02/2021
1314/02/2021
1421/02/2021
1528/02/2021
Sheet1
Cell Formulas
RangeFormula
B6:B15B6=AGGREGATE(15,6,AllDates/((AllDates=EOMONTH(AllDates,0))+(WEEKDAY(AllDates,2)=7)>0),ROW(B1))
Named Ranges
NameRefers ToCells
Enddate=Sheet1!\$B\$2B6:B15
Startdate=Sheet1!\$B\$1B6:B15

#### footoo

##### Well-known Member
Also : =MIN(EOMONTH(A1+1,0),A1+8-WEEKDAY(A1,1))

#### Dossfm0q

##### Banned User
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

Excel Formula:
``=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"),"")``

Replies
1
Views
70
Replies
4
Views
70
Replies
0
Views
68
Replies
4
Views
50
Replies
9
Views
364

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back