Looking for possible automation with Months and weeks on a template

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
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:

1611324485690.png


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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
Maybe yoy can use this for the first week of a year:

eerste dag en week van het jaar.xlsx
ABCDE
14-1-202111-1-202118-1-202125-1-20211-2-2021
212345
Blad1
Cell Formulas
RangeFormula
A1A1=DATE(YEAR(TODAY()),1,1)-(WEEKDAY(DATE(YEAR(TODAY()),1,1),2)-1)+IF(ISOWEEKNUM(DATE(YEAR(TODAY()),1,1))<>1,7,0)
B1:E1B1=A1+7
A2:E2A2=ISOWEEKNUM(A1)
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
Maybe yoy can use this for the first week of a year:

eerste dag en week van het jaar.xlsx
ABCDE
14-1-202111-1-202118-1-202125-1-20211-2-2021
212345
Blad1
Cell Formulas
RangeFormula
A1A1=DATE(YEAR(TODAY()),1,1)-(WEEKDAY(DATE(YEAR(TODAY()),1,1),2)-1)+IF(ISOWEEKNUM(DATE(YEAR(TODAY()),1,1))<>1,7,0)
B1:E1B1=A1+7
A2:E2A2=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:

1611330295881.png
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

=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

Well-known Member
Joined
Mar 5, 2018
Messages
1,378
Office Version
  1. 2016
Platform
  1. Windows
Hi bh24524,

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

Cell Formulas
RangeFormula
H9:BH9H9=IF(H10="","",IF(MONTH(G10)=MONTH(H10),"",TEXT(H10,"mmm")))
G10G10=DATE(RIGHT(A2,4),1,1)+(6-WEEKDAY(DATE(RIGHT(A2,4),1,1),3))
H10:BH10H10=IF(G10="","",IF(YEAR(G10+7)<>YEAR(G10),"",G10+7))
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643

ADVERTISEMENT

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
 
Solution

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
Awesome! I will try this and see if I can somehow work that in.
 

bh24524

Board Regular
Joined
Dec 11, 2008
Messages
224
Office Version
  1. 2013
  2. 2007
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,224
Messages
5,623,482
Members
415,973
Latest member
charlesbm

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