Looking for possible automation with Months and weeks on a template

[bh24524]

I am putting together a chart template that will be distributed among warehouse associates to pick weeks of vacation. This is an ongoing project that I have several posts about but long story short, we currently use a program which might no longer be compatible with a Windows upgrade we are eventually getting here at work. I am trying to do in excel what this program exports to a chart in Excel based on the information that is def into that program. Here is a piece of what the template looks like that is this topic's concern:

Currently as you see, it will list the Month and then right below it the dates that begin the weeks in those months. What I am looking for is to see if there might be a way to automate the setup of future years. I am wondering if there might be a way to have it auto-rearrange months and dates based on the year entered or what current year we are in even.

The month of July for example as you see in 2021 is a month that has 4 beginning Sunday dates. Well in 2022, it has 5 beginning Sunday dates: 3,10,17,24, and 31. This would conflict with the current chart alignment because the 31st would go under the AUG. heading. The program that exports these charts somehow automatically realigns it to account for 5 Sunday months vs 4 Sunday months placing the Month names where appropriate, but I am just curious if there might be something in Excel that could do that? Any help/suggestions are appreciated.

 

[bh24524]

If it helps any, G9 and G10 will always be fixed, G9 will always be Jan and G10 will always be the start of the firt week of the year. I didn't know if maybe that might be something that could be built upon for the rest of the weeks and months.

 

[mart37]

Maybe yoy can use this for the first week of a year:

eerste dag en week van het jaar.xlsx
	A	B	C	D	E
1	4-1-2021	11-1-2021	18-1-2021	25-1-2021	1-2-2021
2	1	2	3	4	5
Blad1

Cell Formulas
Range	Formula
A1	A1	=DATE(YEAR(TODAY()),1,1)-(WEEKDAY(DATE(YEAR(TODAY()),1,1),2)-1)+IF(ISOWEEKNUM(DATE(YEAR(TODAY()),1,1))<>1,7,0)
B1:E1	B1	=A1+7
A2:E2	A2	=ISOWEEKNUM(A1)

 

[bh24524]

Maybe yoy can use this for the first week of a year:

eerste dag en week van het jaar.xlsx
	A	B	C	D	E
1	4-1-2021	11-1-2021	18-1-2021	25-1-2021	1-2-2021
2	1	2	3	4	5
Blad1

Cell Formulas
Range	Formula
A1	A1	=DATE(YEAR(TODAY()),1,1)-(WEEKDAY(DATE(YEAR(TODAY()),1,1),2)-1)+IF(ISOWEEKNUM(DATE(YEAR(TODAY()),1,1))<>1,7,0)
B1:E1	B1	=A1+7
A2:E2	A2	=ISOWEEKNUM(A1)

Hi, Thanks for the response. I think we almost got it. Could the formula be modified to start with Sunday's date, the 3rd? Also with that, would it by chance be possible to show it as the the Day number? So as in the screenshot, a 3 or 03 even in the box? And then the same with subsequent boxes so it looks like this:

 

[mart37]

=DATE(YEAR(TODAY()),1,1)-(WEEKDAY(DATE(YEAR(TODAY()),1,1),2)-0)+IF(ISOWEEKNUM(DATE(YEAR(TODAY()),1,1))<>1,7,0)
For showing only daynumber: Cellformat - Number - Advanced - d or dd

 

[Toadstool]

Hi bh24524,

The start is taken from the last four characters of cell A2.
Row 10 is formatted Custom d

BH24524-b.xlsx
							G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
2
3
4
5
6
7
8
9							Jan					Feb				Mar				Apr				May					Jun				Jul				Aug					Sep				Oct					Nov				Dec
10							3	10	17	24	31	7	14	21	28	7	14	21	28	4	11	18	25	2	9	16	23	30	6	13	20	27	4	11	18	25	1	8	15	22	29	5	12	19	26	3	10	17	24	31	7	14	21	28	5	12	19	26
Sheet1

Cell Formulas
Range	Formula
H9:BH9	H9	=IF(H10="","",IF(MONTH(G10)=MONTH(H10),"",TEXT(H10,"mmm")))
G10	G10	=DATE(RIGHT(A2,4),1,1)+(6-WEEKDAY(DATE(RIGHT(A2,4),1,1),3))
H10:BH10	H10	=IF(G10="","",IF(YEAR(G10+7)<>YEAR(G10),"",G10+7))

 

[Tetra201]

If it helps any, G9 and G10 will always be fixed, G9 will always be Jan and G10 will always be the start of the first week of the year...

See if the following setup works for you:
1) In cell G10, enter 3-Jan-2021 and custom-format the cell as d
2) In cell H10, enter =G10+7 and custom-format the cell as d
3) In cell H9, enter =IF(MONTH(G10)=MONTH(H10),"",TEXT(H10,"mmm"))
4) Drag-copy range H9:H10 to the right as needed

 

[bh24524]

Awesome! I will try this and see if I can somehow work that in.

 

[bh24524]

Thank you all for the solutions! I even tested it out with future years and it is working exactly how I wanted. This is the automation I was after to the "T". There is so much that is going into this project but one by one, hurdles are begin overcome so thank you all very much!