Looking to make a calendar that changes when the month and year changes

[Iccreamann21]

Any advice on how to do this?

 

[Iccreamann21]

Or am I going about this the hard way? The list will be distributed to a dozen people and I didn't want to make it to difficult for them to modify the calendar day by day versus just changing the month/year every every month.

 

[wideboydixon]

Hi. Something like this might work for you:

Book1
	A	B	C	D	E	F	G
1		Cell:	Shift: 1st	Beginning Date:	10/01/2017	Ending Date:	11/01/2017
2				October-17
3
4	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5	10/1	10/2	10/3	10/4	10/5	10/6	10/7
6	10/8	10/9	10/10	10/11	10/12	10/13	10/14
7	10/15	10/16	10/17	10/18	10/19	10/20	10/21
8	10/22	10/23	10/24	10/25	10/26	10/27	10/28
9	10/29	10/30	10/31	11/1
10
11
Sheet3

Cell Formulas
Range	Formula
D2	=E1
A5	=IF(WEEKDAY($D$2,1)=COLUMN(),$D$2,"")
A6	=IF(G5<$G$1,G5+1,"")
B5	=IF(WEEKDAY($D$2,1)=COLUMN(),$D$2,IF(A5="","",A5+1))
B6	=IF(A6="","",IF(A6<$G$1,A6+1,""))

Copy B5 formula across to G5. Copy B6 formula across to G6. Then copy A6:G6 down to row 7-11 or as far as you need.

WBD

 

[Iccreamann21]

You are my Hero! Thread can be closed. This is exactly what I was looking for!

Thank you again!

 

[Iccreamann21]

Hi. Something like this might work for you:

	A	B	C	D	E	F	G
Cell:	Beginning Date:	Ending Date:

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

[TD="align: center"]Shift: 1st[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]10/01/2017[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]11/01/2017[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]October-17[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/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"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Sunday[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Monday[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Tuesday[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Wednesday[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Thursday[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Friday[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Saturday[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]10/1[/TD]
[TD="align: center"]10/2[/TD]
[TD="align: center"]10/3[/TD]
[TD="align: center"]10/4[/TD]
[TD="align: center"]10/5[/TD]
[TD="align: center"]10/6[/TD]
[TD="align: center"]10/7[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]10/8[/TD]
[TD="align: center"]10/9[/TD]
[TD="align: center"]10/10[/TD]
[TD="align: center"]10/11[/TD]
[TD="align: center"]10/12[/TD]
[TD="align: center"]10/13[/TD]
[TD="align: center"]10/14[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]10/15[/TD]
[TD="align: center"]10/16[/TD]
[TD="align: center"]10/17[/TD]
[TD="align: center"]10/18[/TD]
[TD="align: center"]10/19[/TD]
[TD="align: center"]10/20[/TD]
[TD="align: center"]10/21[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]10/22[/TD]
[TD="align: center"]10/23[/TD]
[TD="align: center"]10/24[/TD]
[TD="align: center"]10/25[/TD]
[TD="align: center"]10/26[/TD]
[TD="align: center"]10/27[/TD]
[TD="align: center"]10/28[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]10/29[/TD]
[TD="align: center"]10/30[/TD]
[TD="align: center"]10/31[/TD]
[TD="align: center"]11/1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet3​

[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] "]D2[/TH]
[TD="align: left"]=E1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]=IF(WEEKDAY($D$2,1)=COLUMN(),$D$2,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=IF(WEEKDAY($D$2,1)=COLUMN(),$D$2,IF(A5="","",A5+1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A6[/TH]
[TD="align: left"]=IF(G5<$G$1,G5+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=IF(A6="","",IF(A6<$G$1,A6+1,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Copy B5 formula across to G5. Copy B6 formula across to G6. Then copy A6:G6 down to row 7-11 or as far as you need.

WBD

Last Question! In Cell G1, How do I make it so that it shows only the exact days of that month. So for October I would want it to say 10/31/2017, but in November I would want it to say 11/30/2017?

 

[wideboydixon]

Formula for G1:

=EOMONTH(E1,0)

WBD