Date sequencing formula - earliest of next Sunday and month end

learjsy

New Member
Joined
Feb 16, 2018
Messages
12
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
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Feb 16, 2018
Messages
12
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
Joined
Mar 22, 2020
Messages
708
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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))
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,502
Office Version
  1. 365
Platform
  1. Windows
Solution

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
708
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

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
Joined
Sep 21, 2016
Messages
3,235
Office Version
  1. 2016
Platform
  1. Windows
Also : =MIN(EOMONTH(A1+1,0),A1+8-WEEKDAY(A1,1))
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
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"),"")
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top