Making a SUNDAY ONLY dates list

[ashhole1207mk]

How would I enter a formula to make a SUNDAY ONLY date list in excel? I am not very good at excel but I am working on it.

I found one online but i cannot edit it to continue further into 2025, it stops at January. See attached for example of the one I found on google.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1		25/08/2024
2		01/03/2025
3
4		25/08/2024
5		01/09/2024
6		08/09/2024
7		15/09/2024
8		22/09/2024
9		29/09/2024
10		06/10/2024
11		13/10/2024
12		20/10/2024
13		27/10/2024
14		03/11/2024
15		10/11/2024
16		17/11/2024
17		24/11/2024
18		01/12/2024
19		08/12/2024
20		15/12/2024
21		22/12/2024
22		29/12/2024
23		05/01/2025
24		12/01/2025
25		19/01/2025
26		26/01/2025
27		02/02/2025
28		09/02/2025
29		16/02/2025
30		23/02/2025
31
Data

Cell Formulas
Range	Formula
B4:B30	B4	=LET(d,NETWORKDAYS.INTL(B1,B2,"1111110"),WORKDAY.INTL(B1-1,SEQUENCE(d),"1111110"))
Dynamic array formulas.

 

[KOKOSEK]

According to you signature (XL365) lets go with:

=SEQUENCE(520,1,"25/08/24",7)

All Sundays for next 10 years.

 

[ashhole1207mk]

According to you signature (XL365) lets go with:

=SEQUENCE(520,1,"25/08/24",7)

All Sundays for next 10 years.

can you walk me through it???? i have NO CLUE what i am doing when it comes to doing this, why i was looking on google for a template..

thank you for any and all of your help

 

[KOKOSEK]

SEQUENCE generating data according to parameters. So in our case we generate:
- 520 dates / days (how many rows - 1st parameter)
- in 1 column (2nd parameter)
- 3rd parameter is start from, so we've put starting date which we know it is Sunday
- 4th parameter is step, so 7.
In this way you get Sundays for next 10 years.

 

[Scott R]

Enter the first Sunday in your list, i.e. 9/1/2024 in A1.
In A2, enter =A1+7
Copy A2 to A3 and beyond to meet your needs.

 

[alansidman]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start])..Number.From([End])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if Date.DayOfWeek([Custom])=0 then [Custom] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Custom.1"})
in
    #"Removed Other Columns"

Book2
	A	B	C	D
1	Start	End		Custom.1
2	9/1/2024	3/1/2025		9/1/2024
3				9/8/2024
4				9/15/2024
5				9/22/2024
6				9/29/2024
7				10/6/2024
8				10/13/2024
9				10/20/2024
10				10/27/2024
11				11/3/2024
12				11/10/2024
13				11/17/2024
14				11/24/2024
15				12/1/2024
16				12/8/2024
17				12/15/2024
18				12/22/2024
19				12/29/2024
20				1/5/2025
21				1/12/2025
22				1/19/2025
23				1/26/2025
24				2/2/2025
25				2/9/2025
26				2/16/2025
27				2/23/2025
Sheet1