Creating your own Calendar Templates

[AndrewPilcher]

Hello, I see some great templates for excel regarding calendars. When trying to create my own, I am having difficulty skipping rows or controlling layout (one row or grid with 7 columns).

There seems to be little documentation about DaysAndWeeks and when compared to templates I can only seem to execute the function as =DaysAndWeeks but all the templates show the {=} format.

|:   Where can I find some documentation on taking advantage of these powerful features?

DaysAndWeeks do not appear as intellisense or in the help pane. A search in support only returns the WEEKDAY function. 

[#2]

@AndrewPilcher DAYSANDWEEKS is not an Excel function. What templates have you been looking at and what do you want to achieve?

[#3]

Start with the evergreen calendar in the excel templates section to see an example of single row month, look here https://templates.office.com/en-us/evergreen-calendar-tabs-white-tm00000047 for the calendar that splits the rows over weeks including a skipped row to place data. I would like to be able to recreate this but cannot get the behavior consistent. Have you tried entering =DaysAndWeeks into a cell?

[#4]

@AndrewPilcher 

That's not a function, that is named array constant

[#5]

Thank you @Sergei Baklan for the clarification. Can you make sense of how to get the array definition {=DaysAndWeeks+Today()} to fill alternating rows by week?

[#6 · Accepted solution]

@AndrewPilcher I don't understand what you mean by fill alternating rows by week.

@Sergei Baklan , @Riny_van_Eekelen , I don't understand the point of that named value being a 2D array because the best I could tell it is only using the {0,1,2,3,4,5,6} first row of it.

EDITTED: deleted file and ref to it
Back to @AndrewPilcher  question, the way that template work is to create an ARRAY of 7 cells in a row to output a particular week.  So cells B3:H3 are all part of week 1 of the month, B7:H7 are wk2, etc....

If you want to skip more rows then just insert rows in between.  If you want to skip weeks then just add to the "offset" being used in the calculation for each week.  In template each week looks like:

{=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="mon")+1)+offset}  where offset is 1 for wk1, 8 for wk2, ...

does that help?

[#7]

@mtarler 

I have no pre-DA Excel to test, but named arrays shall work natively in it, i.e. without CSE. And sure it returns 2D array in DA Excel.

[#8]

Thank you, I did not realize these were similar defined names with different formulas in the various workbooks.