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

 

[excellence]

Fantastic !!
I can't thank you enough for continuing to help me with this.
I really think I got it now. Took long enough )

Sorry for all the posts.

 

[Dave Patton]

Thanks for the feedback.

You could now read posts 8 and 12.
Naming the formula with Lambda makes the formula more flexible and user-friendly.

1. We require a calendar for the year 2023.
2. We produce the calendar by using the function CalYear(2023) the stated parameter is "Year";
" year " can be specified like CalYear(2023) or with a cell reference like CalYear(a1)

The function is named in Name Manager. The name is CalYear
Value is
=LAMBDA(Year,VSTACK({"Sun","Mon","Tue","Wed","Thurs","Fri","Sat"}, LET(d,DATE(Year,1,1),dt,SEQUENCE(53,7,WORKDAY.INTL(d+1,-1,"1111110"),1),dt)))

 

[excellence]

Hi,
I decided to take a day off of this.
At least I got it to work, pretty amazing.
A question that I have is how does one know what names can be used in name manager ( i.e. CalYear and CalMonth)? These "names or functions" do not seem to be listed in the Excel function section. That said, how does one know what other names can be used with LAMDA?

Thanks

 

[Dave Patton]

A question that I have is how does one know what names can be used in name manager ( i.e. CalYear and CalMonth)? These "names or functions" do not seem to be listed in the Excel function section. That said, how does one know what other names can be used with LAMDA?

I don't know the naming conventions off hand. Try a name that you think is logical and if the name is already used or not acceptable, the system will probably advise. Just use the regular naming conventions.

The function should show when you start to type Cal ... and the parameter will also show.

I just combined the month and year into one function. I named it Cal1or12Months. Do you want to try it?

The latter suggestions do not include the test whether of not the date is in the selected month or year.
One can use conditional formatting to shade or hide the out of range dates.

 

[excellence]

I just changed the name in name manager for the month calendar and named it xxx and put =xxx(a1) into a2 while putting 23-Apr into a1 and it worked. Kept your LAMDA formula. So, it seems that one can use any name (probably avoiding listed function names) and like you did, giving it a name that would make some sense like CalYear or CalMonth