Sequence formula on every second row for Calendar type view

[ninjaturtle131313]

Hi,

is it possible to adjust the =SEQUENCE() formula so that only every second row is filled?

I am trying to create a calendar within excel so that the dates automatically populate in the correct cell relevant to the day of the week. Picture attached.

However the sequence formula spills across cells and id like it to skip a row so that i can write information underneath each date.

 

[Dave Patton]

Two ideas
1. Format the cells so that there is space for text.
2. Create the calendar and then insert rows
Convert the calendar to Values and format the dates to your preference.

Calendar_2023a.xlsm
	A	B	C	D	E	F	G
1	1		Calendar 2023
2	1-Mar-23
3	Sun	Mon	Tue	Wed	Thurs	Fri	Sat
4				01-Mar-23	02-Mar-23	03-Mar-23	04-Mar-23
5	05-Mar-23	06-Mar-23	07-Mar-23	08-Mar-23	09-Mar-23	10-Mar-23	11-Mar-23
6	12-Mar-23	13-Mar-23	14-Mar-23	15-Mar-23	16-Mar-23	17-Mar-23	18-Mar-23
7	19-Mar-23	20-Mar-23	21-Mar-23	22-Mar-23	23-Mar-23	24-Mar-23	25-Mar-23
8	26-Mar-23	27-Mar-23	28-Mar-23	29-Mar-23	30-Mar-23	31-Mar-23
9
MonthorYear

Cell Formulas
Range	Formula
C1	C1	="Calendar "&TEXT(A2,"yyyy")
A3:G9	A3	=Cal1or12Months(A1,A2)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=$Q$1:$Q$2

 

[ninjaturtle131313]

thanks for your response. I was hoping to have individual cells for the information underneath so that i can add in a formula to pull the info from another tab. and so that it is a dynamic calendar, i.e. when i select a different month at the top left, it changes the dates and pulls the data for that month instead.

hope this makes sense

 

[Dave Patton]

I prepared a variety of calendars with different formatting. Since I wanted to add information to certain cells, I built the calendar for the entire year.
I then converted the sheet to values and formatted the calendar for my requirements.
You could insert rows after each week.
You could add a column at the end and filter by month; all the information could be on one sheet.

 

[ninjaturtle131313]

This is the template i am using:
Student assignment planner
i downloaded it but it is coming up with #Value errors as i think its formatted for US dates and not Australia. I can't seem to get it to work, but that template is how im wanting it, so that i dont have to have different tabs for each month.

 

[Dave Patton]

What formula(s) does the template use?
Can you edit the template?
Can you add information to the cells?

Do you want to build your own sheet for the year?

 

[Dave Patton]

What is the usual format for an Australian Calendar?
Do you want to use that format?

You could have a formula create a dynamic range for the entire year.
A simple formula could stack the year twice for your working calendar.
You would have the dates for each week shown twice and you could format each row to your preference.

Calendar_2023a.xlsm
	A	B	C	D	E	F	G
1				2023
2	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3	1-Jan-23	2-Jan-23	3-Jan-23	4-Jan-23	5-Jan-23	6-Jan-23	7-Jan-23
4	1-Jan-23	2-Jan-23	3-Jan-23	4-Jan-23	5-Jan-23	6-Jan-23	7-Jan-23
5	8-Jan-23	9-Jan-23	10-Jan-23	11-Jan-23	12-Jan-23	13-Jan-23	14-Jan-23
6	8-Jan-23	9-Jan-23	10-Jan-23	11-Jan-23	12-Jan-23	13-Jan-23	14-Jan-23
Year duplicated

 

[Fluff]

Change the formula in B6 to

=IF(WEEKDAY(DATE(MoYear, MoMonthNum,1))=COLUMN(A$2),1,IF(LEN(A6)>0,A6+1,""))

and drag across.