Format Monthly calendar

[excellence]

Trying to put the date (just number) into the respective cells of a monthly calendar based on what exists in cell a1, which in this case is 11/1/23.
Appreciate any help

Many thanks

 

[Dave Patton]

It is Ok to ask questions. That is why the forum exists,


In the January 2023 cell I don't understand =DATE(B1,MONTH(--(1&B2)),1)
With this example, the year is specified in one cell B1 and the month is specified in B2.
The Date function uses a number for the month. Text of say January or Jan can be coerced to a number by the MONTH(--(1&B2)).

I have other questions like what dt and D2 are...variables?

Let save the information as a variable

LET(d,A1-DAY(A1)+1,dt,SEQUENCE(6,7,WORKDAY.INTL(d+1,-1,"1111110"),1)

d= A1-DAY(A1)+1 which calculates the first day of the month
dt= the sequence information
6 number of rows
7 number of columns
WORKDAY.INTL(d+1,-1,"1111110") calculates the first Sunday
1 increment by 1

What part of the formula tells it to start on Sunday? WORKDAY.INTL(d+1,-1,"1111110") calculates the first Sunday
d is the date of the 1st of the month. I added one in case the 1st of the month is a Sunday and the -1 to find the previous Sunday

d2 in post #3 is the calculated date Jan 1 2023 custom formatted to mmm yyyy

Recommendation for each part of the formula, prepare an example and review it with Excels Formula Evaluate.
Then build a solution with your preferences.

 

[Dave Patton]

N.B. correction post #9 should be

Calendar For month
Example CalMonth(Date)
The function is named CalMonth
=LAMBDA(Date,VSTACK({"Sun","Mon","Tue","Wed","Thurs","Fri","Sat"}, LET(d,Date-DAY(Date)+1,dt,SEQUENCE(6,7,WORKDAY.INTL(d+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=d,dt,""))))

 

[excellence]

It is Ok to ask questions. That is why the forum exists,


In the January 2023 cell I don't understand =DATE(B1,MONTH(--(1&B2)),1)
With this example, the year is specified in one cell B1 and the month is specified in B2.
The Date function uses a number for the month. Text of say January or Jan can be coerced to a number by the MONTH(--(1&B2)).

I have other questions like what dt and D2 are...variables?

Let save the information as a variable

LET(d,A1-DAY(A1)+1,dt,SEQUENCE(6,7,WORKDAY.INTL(d+1,-1,"1111110"),1)

d= A1-DAY(A1)+1 which calculates the first day of the month
dt= the sequence information
6 number of rows
7 number of columns
WORKDAY.INTL(d+1,-1,"1111110") calculates the first Sunday
1 increment by 1

What part of the formula tells it to start on Sunday? WORKDAY.INTL(d+1,-1,"1111110") calculates the first Sunday
d is the date of the 1st of the month. I added one in case the 1st of the month is a Sunday and the -1 to find the previous Sunday

d2 in post #3 is the calculated date Jan 1 2023 custom formatted to mmm yyyy

Recommendation for each part of the formula, prepare an example and review it with Excels Formula Evaluate.
Then build a solution with your preferences.

I appreciate all of your updates, but my knowledge is pretty rudimentary.
So, if we could stick with this formula you posted:

=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=D2,dt,""))

I read your post and have been working on it endlessly
USING THIS FORMULA
=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=D2,dt,""))

My read is that:

1 this translates to 7 columns by 6 rows
2 the D2+1,-1 I don't understand..I read your reasoning but don't understand...is the D2+1,-1 the Sundays you are referring to? Why do you need to add 1 if the 1st is a Sunday or subtract 1 for the previous Sunday? And why is the + or - 1 treated as a week in the formula if my "reasoning" is right?
3 the 1111110 translates to counting Sundays and incrementally counting Sundays by 1...so if 1/1/23 is a Sunday, it would next yield 1/8/23 as a Sunday?
4 How does the formula know which day of the week to start on in any given month?

As always
Many thanks

 

[Dave Patton]

Re your questions


1 this translates to 7 columns by 6 rows Correct.
2 the D2+1,-1 I don't understand. I read your reasoning but don't understand...is the D2+1,-1 the Sundays you are referring to? Why do you need to add 1 if the 1st is a Sunday or subtract 1 for the previous Sunday? And why is the + or - 1 treated as a week in the formula if my "reasoning" is right?

D2 has the date 1st day of month in this case Jan 2023. It may be easier to understand with a different month.
D2 +1 means from the 2nd day of the month and -1 look to previous work day
with Workday.intl the 0 means workday so 1111110 means look for Sunday
the formula finds the 1st Sunday before jan 2 which happens to Jan 1, If we did not add the one it would find a Sunday late in the previous month.
Try the formula for different months.

If the first Sunday is in the previous month, it does not show. Near the end of the formula, there is a test, ,IF(dt-DAY(dt)+1=D2,dt,"")
In English, this states if date produced by sequence minus the day + 1 = D2 (1st of month) then the date is in the month so show date produced by sequence.
Depending on the month, 1 -6 dates in the first row will shows as blank, since the date generated is in the previous month; the same logic applies to the last row(s).
N.B. The sequence produces 7*6= 42 days and months have 28 - 31 days and only the dates for the specified month show.

3 the 1111110 translates to counting Sundays and incrementally counting Sundays by 1...so if 1/1/23 is a Sunday, it would next yield 1/8/23 as a Sunday? No

The Workday.intl with 1111110 and -1 finds the first Sunday before day 2. That is the starting date for the sequence.

4 How does the formula know which day of the week to start on in any given month?
It start on the first Sunday before the date as mentioned above. If that date is in the previous month, the test mentioned above puts "" i.e. blank in the cell for Monday.

I hope this helps Dave

 

[Dave Patton]

I posted the calendar for March 2023.
Feb 26 is the last Sunday in Feb and Feb 26, 27, and 28 do not show because they are not March dates.
March 1 is Wednesday.
N.B. You can copy the post to a clean sheet. Click the icon below the f(x) in the heading and then move to your sheet and paste in cell A1.

Calendar_Examples.xlsm
	A	B	C	D	E	F	G
1	Year	2023
2	Month	March		March 2023
3
4	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5				1-Mar-23	2-Mar-23	3-Mar-23	4-Mar-23
6	5-Mar-23	6-Mar-23	7-Mar-23	8-Mar-23	9-Mar-23	10-Mar-23	11-Mar-23
7	12-Mar-23	13-Mar-23	14-Mar-23	15-Mar-23	16-Mar-23	17-Mar-23	18-Mar-23
8	19-Mar-23	20-Mar-23	21-Mar-23	22-Mar-23	23-Mar-23	24-Mar-23	25-Mar-23
9	26-Mar-23	27-Mar-23	28-Mar-23	29-Mar-23	30-Mar-23	31-Mar-23
Calendar

Cell Formulas
Range	Formula
D2	D2	=DATE(B1,MONTH(--(1&B2)),1)
A5:G10	A5	=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=D2,dt,""))
Dynamic array formulas.

 

[excellence]

dt)+1=D2,dt,""))[/XD][/XR][/RANGE]

 

[excellence]

You are reading my mind )

I am still digesting your next to last post, but think I am closer than ever to understanding this. Your last post leads to my question:

How do I test the IF statement at the end of your formula? It obviously works, but how to subject it to a test like 44958 =2/1/23 and does not appear in the 1/23 calendar which is appropriate. Or better yet, just explaining how this works

IF(dt-DAY(dt)+1=D2,dt,"")

So, in a 1/2023 calendar how could I put numbers in the above IF statement to see if it does/does not meet criteria for putting the date in or leaving it out

I just can't wrap my mini mind around that IF statement.

Your help has been nothing short of magnificent


On a separate note, when I click on reply to your last post, I get this:
Am I doing something wrong?

I posted the calendar for March 2023.
Feb 26 is the last Sunday in Feb and Feb 26, 27, and 28 do not show because they are not March dates.
March 1 is Wednesday.
N.B. You can copy the post to a clean sheet. Click the icon below the f(x) in the heading and then move to your sheet and paste in cell A1.

Calendar_Examples.xlsm
	A	B	C	D	E	F	G
1	Year	2023
2	Month	March		March 2023
3
4	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5				1-Mar-23	2-Mar-23	3-Mar-23	4-Mar-23
6	5-Mar-23	6-Mar-23	7-Mar-23	8-Mar-23	9-Mar-23	10-Mar-23	11-Mar-23
7	12-Mar-23	13-Mar-23	14-Mar-23	15-Mar-23	16-Mar-23	17-Mar-23	18-Mar-23
8	19-Mar-23	20-Mar-23	21-Mar-23	22-Mar-23	23-Mar-23	24-Mar-23	25-Mar-23
9	26-Mar-23	27-Mar-23	28-Mar-23	29-Mar-23	30-Mar-23	31-Mar-23
Calendar

Cell Formulas
Range	Formula
D2	D2	=DATE(B1,MONTH(--(1&B2)),1)
A5:G10	A5	=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=D2,dt,""))
Dynamic array formulas.

 

[Dave Patton]

How do I test the IF statement at the end of your formula?

I show 2 examples, the first is not in the month in question and the second is within the month (March).
Paste the example to K1 on a clean sheet.
Try Formulas Formula Evaluate on the 2 Formulas

Calendar_Examples.xlsm
	J	K	L	M
1	Before Test	Not in Month		In Month
2	D2=	1-Mar-23	D2=	1-Mar-23
3	C5=	28-Feb-23	G5=	4-Mar-23
4	Formula			4-Mar-23
Calendar

Cell Formulas
Range	Formula
K2	K2	=D2
K4,M4	K4	=IF(K3-DAY(K3)+1=K2,K3,"")
M2	M2	=D2
M3	M3	=G5

On a separate note, when I click on reply to your last post, I get this:
Am I doing something wrong?

I am not sure of your question.

I would just go where it say "write your reply" . Write some information and then "Post Reply" " if necessary Edit and "Save"

 

[Dave Patton]

It may be easier to just enter the information in a sheet and then review the 2 formulas with Formulas Evaluate.
I probably linked some of the data to my sheet.

 

[Dave Patton]

We could use a test with Month. See the 2 alternatives below

Calendar_Examples.xlsm
	A	B	C	D	E	F	G	H
1	Year	2023
2	Month	Mar		1-Mar-23
3
4	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5	26-Feb-23	27-Feb-23	28-Feb-23	1-Mar-23	2-Mar-23	3-Mar-23	4-Mar-23
6				1-Mar-23	2-Mar-23	3-Mar-23	4-Mar-23	version 1
7				1-Mar-23	2-Mar-23	3-Mar-23	4-Mar-23	version 2
8
xx

Cell Formulas
Range	Formula
A6:G6	A6	=IF(A5-DAY(A5)+1=$D$2,A5,"")
A7:G7	A7	=IF(MONTH(A5)=MONTH($D$2),A5,"")