Adapting a linear annual calendar from data used to generate monthly annual calendars

GREETINGS OZGRID!

So we've been manually updating this monstrosity of a linear school calendar at work for several years (may be NSFL): [ATTACH=JSON]{"data-align":"center","data-size":"large","data-attachmentid":1200188}[/ATTACH]
THIS year we have a new superintendent, and you can imagine how excited I got when he suggested that we redesign the calendar. I went out and found a pre-made annual calendar with a spin button that changes the displayed year and automatically updates the layout of the corresponding dates in the cells, which I haven't been able to entirely figure out. But after several days of intense Google Fu, what I managed to come up with is a spreadsheet that takes manually entered data for the dates of our various events on the first sheet, and automatically generates calendars in the two formats we use: a parent calendar and a staff calendar.

Unfortunately I actually like the people I work with, so I don't want them to lose the functionality of the old linear calendar with the redesign. My current solution is to include a calendar in that format in the spreadsheet that will be generated from the same manually entered data, and this is where I'm stuck.

I attached the spreadsheet, and here's a summary of how it works: The pre-made calendar had formulas in the cells that referred to Names that appear to determine the first day of the month by referencing the value of the cell that contains the year, as near as I can tell. There's a Name for each month in the format of AprSun1, AugSun1, DecSun1, etc. etc. that are structured like this (using August as an example):

=DATEVALUE("8/1/"&'Staff'!$P$5)-WEEKDAY(DATEVALUE("8/1/"&'Staff'!$P$5))+1

Then the formulas in each cell look like this:

=IF(AND(YEAR(AugSun1+1)=$P$5,MONTH(AugSun1+17)=8),AugSun1+1, "")

With cell P5 containing the year, and the +1 after AugSun1 incrementing by one for subsequent cells (and by 3 when wrapping around from Friday to Monday). I managed to split the year by putting =P5+1 in cell V5, and then changing $P$5 to $V$5 in the Name formulas for JanSun1 through JulySun1. I also created a "FirstDay" Name that's an absolute reference to the value in the "First Day of School" cell in the Data sheet (cell K6), and an "AugMonth" Name that's an absolute reference to all of the date cells in August on the Staff sheet (cells A9:E14).

So in the linear calendar, I'm able to reference "FirstDay" with a VLOOKUP and fill the top "First Week" columns with the dates for the first week of school by using this formula:

=VLOOKUP(FirstDay,AugMonth,1,TRUE)

I incremented the column number in the VLOOKUP by 1 for each subsequent cell in the week, and it fills those in just fine. Then in the second week, I copied all of those formulas over but added a +7 after "FirstDay":

=VLOOKUP(FirstDay+7,AugMonth,1,TRUE)

I was hoping that incrementing the added amount by 7 would work for every subsequent week since it would just increase the date code by 7 each time, but this fell apart once I got to +21 and the formula starting returning the dates for the last week of August again. I'm pretty certain I need to do something like include the cells for the rest of the months into the "AugMonth" Name, and then use INDEX instead of VLOOKUP so I can include an area number as well? I haven't played with it yet, but that's what I'm thinking.

The other problem is even if I increment the dates of the subsequent weeks correctly, every cell in each row in the original linear calendar was actually two cells merged vertically, so that the first day of a new month could be unmerged and the name of the month included in the top cell. I have no idea how to have the formula check to see if the date in a cell is the first day of a new month, and to include a mmm format label when it does. So far I've done everything with cell formulas and conditional formatting, since nothing seemed intense enough for a VBA script. But I'm not sure which approach I should take to best achieve this result, or if there might be an easier to read format for the linear calendar or something.

Do you wizards have any advice or suggestions on how to do this? I'd appreciate any help you can give me!

Thanks! Google did most of the heavy lifting, but I'll totally take credit for it :wink:

Ah yes, the calculation from the current calendar. I can upload it, but... how can I explain... Well it's mostly an INDEX function, in that the user takes their index finger and counts how many attendance days are in the row, and then manually types the number in the right column. You know, like a caveman would. I believe this is also why they use shapes instead of fill colors to indicate which dates have events. It's easier to understand when drawing similar shapes on stone walls is a large portion of your ability to communicate.

The current idea I have is to use the hidden sheet where the reference cells for the drop down list are, and copy the formulas from the Staff sheet's calendar cells to it, but arranged into a single column. I feel like it'd be way easier to reference that than trying to use the existing calendar cells as an array. Does that sound like a good approach?? It kinda feels like cheating.

Alright, I think I figured out how it's going to work. I found this formula that can reference any date and return the Monday for that week, so I put that in the top left cell of the linear calendar:

=Date-WEEKDAY(Date,2)+1

Every other cell just references that one, and adds a +1 that increments for each column or a +3 if going from Friday to Monday. I cheated having to type +2, +3, +4, +5, etc. etc. by using the COLUMNS function to count $A$1:A1, and then had the fill handle increment it for me. I also found out that you can hit CTRL + J while you're setting up custom formatting to enter a soft return, so I found a conditional formatting rule that identifies the first working day of any month, and set it up to change the formatting to mmm↵d:

=A1=WORKDAY(DATE(YEAR(A1),MONTH(A1),0),1)

Now everything seems to be working like it should!

And I totally see what you meant about calculating the Student Attendance column, it's a lot trickier than it seems. I ended up calculating all of the non attendance days for each row, then subtracting that from a COUNT formula and it's been doing good so far. But only time will tell!