listing Every Monday and Friday's Day of the month

[tame82]

Hello,

I am trying to list out the days of each month for every Monday and Friday in a given year.

I have cell A1 contain a year, for example 2018. Under the year beginning with B1 will be the months listed out. The row below the months, beginning with C1 should list each day of the month a Monday falls on followed by a hyphen, and then the day the following Friday falls on.

For Example

2018
Jan Feb
01-07 08-14 15-21 22-28 29-04

and so on.

I need the formula to fill down the row to base itself on the year provided.

If I can get a reliable way to calculate all the Mondays, the following Fridays should be easy enough. I was close with:
=TEXT(DAY(TEXT(DATE($A$1,1,8)-WEEKDAY(DATE($A$1,1,6)),"00"))+((COLUMN(A3)-COLUMN($A3))*7),"dd")
but it goes awry in March, listing the 4th instead of the 5th.

I can get accurate numbers formatting it this way
=TEXT(DAY(TEXT(DATE($A$1,1,8)-WEEKDAY(DATE($A$1,1,6)),"00"))+((COLUMN(A3)-COLUMN($A3))*7),"00")

But if I do that, I can't find a way to turn that into a day of the month for each month. as apposed to accurate numbers from 1 to 365 in increments of 7.

I'm not great with Excel and this has taken me a while along with plenty of help from this post from excelbanter.com:
https://www.excelbanter.com/excel-discussion-misc-queries/117186-calculate-every-monday.html

Any help is greatly appreciated.

 

[dave2018]

In your example, you list all the Mondays, not fridays... Jan 07 is a sunday...

Not understanding. or was that i typo?

 

[sheetspread]

Excel 2010
	A	B
1	1/1/2018
2	1/1/2018	2
3	1/5/2018	6
4	1/8/2018	2
5	1/12/2018	6
6	1/15/2018	2
7	1/19/2018	6
8	1/22/2018	2
9	1/26/2018	6
10	1/29/2018	2
11	2/2/2018	6
12	2/5/2018	2
13	2/9/2018	6
14	2/12/2018	2
15	2/16/2018	6
16	2/19/2018	2
17	2/23/2018	6
Sheet9

Cell Formulas
Range	Formula
A2	=DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,6))
A3	=A2+4
A4	=A2+7
B2	=WEEKDAY(A2)

highlight a3 and a4 then copy down

 

[sheetspread]

list each day of the month a Monday falls on followed by a hyphen, and then the day the following Friday falls on

Like:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1/1/2018
2	1/1/2018	1-5	8-12	15-19	22-26	29-2	5-9	12-16	19-23	26-2	5-9	12-16	19-23	26-30
Sheet9

Cell Formulas
Range	Formula
A2	=DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,6))
B2	=DAY($A$2+(COLUMN(A1)-1)*7)&"-"&DAY($A$2+4+(COLUMN(A1)-1)*7)

 

[Dave Patton]

Excel 2010
	A	B	C
1	2018	January	01 - 05
2		January	08 - 12
3		January	15 - 19
4		January	22 - 26
5		January	29 - 02
6		February	05 - 09
7
1cc

Cell Formulas
Range	Formula
B1	=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,6))
B2	=B1+7
C1	=TEXT(B1,"dd")&" - "&TEXT(B1+4,"dd")

The Date in B1 etc is custom formatted as "mmmm".
Copy the formulas down.
Edit the formatting if required.

 

[tame82]

Thank you all for your quick and amazing solutions to my issue.

I apologize if I was not great at explaining what I meant. I had hoped the crude text representation would help.

It seems I was overcompensating this solution by a fair margin.

I was able to apply Sheetspread's solution and it worked very well. I appreciate the time you all took to help me.

Like:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1-5	8-12	15-19	22-26	29-2	5-9	12-16	19-23	26-2	5-9	12-16	19-23	26-30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2018[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=DAY($A$2+(COLUMN(A1)-1)*7)&"-"&DAY($A$2+4+(COLUMN(A1)-1)*7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]