Apr 15 2022 09:07 AM
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.
Apr 15 2022 10:53 AM
@AndrewPilcher DAYSANDWEEKS is not an Excel function. What templates have you been looking at and what do you want to achieve?
Apr 15 2022 12:03 PM
Apr 15 2022 12:37 PM
Apr 15 2022 02:37 PM
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?
Apr 15 2022 03:25 PM - edited Apr 15 2022 03:38 PM
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?
Apr 16 2022 02:41 AM
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.
Apr 18 2022 09:01 AM
Apr 15 2022 03:25 PM - edited Apr 15 2022 03:38 PM
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?