Format Monthly calendar

E

excellence

Board Regular
Joined
Oct 5, 2005
Messages
155
Office Version
  1. 365
Platform
  1. MacOS
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

MONTHLY.png


Many thanks
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
The date you cited is ambiguous; I assumed it is the 11th of January 2023.
We can adapt the formula cited for Sun - Sat or Mon- Sun
You can format the results to your requirements.
The full date can show or just the day of the month.

Calendar_Examples.xlsm
ABCDEFG
1January 2023
2
3
4SundayMondayTuesdayWednesdayThursdayFridaySaturday
51234567
6891011121314
715161718192021
822232425262728
9293031
10
Calendar (2)
Cell Formulas
RangeFormula
A5:G10A5=LET(d,A1-DAY(A1)+1,dt,SEQUENCE(6,7,WORKDAY.INTL(d+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=d,DAY(dt),""))
Dynamic array formulas.
 
Upvote 0
Solution
Calendar_Examples.xlsm
ABCDEFG
1Year2023
2MonthJanJanuary 2023
3
4SundayMondayTuesdayWednesdayThursdayFridaySaturday
51-Jan-232-Jan-233-Jan-234-Jan-235-Jan-236-Jan-237-Jan-23
68-Jan-239-Jan-2310-Jan-2311-Jan-2312-Jan-2313-Jan-2314-Jan-23
715-Jan-2316-Jan-2317-Jan-2318-Jan-2319-Jan-2320-Jan-2321-Jan-23
822-Jan-2323-Jan-2324-Jan-2325-Jan-2326-Jan-2327-Jan-2328-Jan-23
929-Jan-2330-Jan-2331-Jan-23
10
Calendar
Cell Formulas
RangeFormula
D2D2=DATE(B1,MONTH(--(1&B2)),1)
A5:G10A5=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=D2,dt,""))
Dynamic array formulas.
 
Upvote 0
excellence said:
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

View attachment 83402

Many thanks
Click to expand...
Dave Patton said:
The date you cited is ambiguous; I assumed it is the 11th of January 2023.
We can adapt the formula cited for Sun - Sat or Mon- Sun
You can format the results to your requirements.
The full date can show or just the day of the month.
Click to expand...
excellence said:
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

View attachment 83402

Many thanks
Click to expand...
Had trouble replying to you directly, but clearly I have a lot to digest with your formulae. Where do I find some startup site to understand it all? Also, I assume I can't superimpose conditional formatting, like shading weekends and holidays?

When I tried to respond to you the webpage filled with a bunch of script, so I responded to myself
 
Upvote 0
"I have a lot to digest with your formulae"

Try working out examples with each part of the formula. For example, A1-DAY(A1)+1 calculates the first day of the month.

" I assume I can't superimpose conditional formatting, like shading weekends and holidays?"

Please look at the second example, it leaves the full date in each cell. You can format the date to your preference even just dd for day.
You can use conditional formatting.
 
Upvote 0
My solution is complicated compared to others offered, but here is a dynamic sunday to saturday calendar (the drop down in your date cell is for testing):
Some Mr Excel Questions.xlsx
ABCDEFGHI
1
210/1/2022
3
41234567
5891011121314
615161718192021
722232425262728
8293031
9
DYNAMIC CALENDAR
Cell Formulas
RangeFormula
B4:H8B4=LET( FIRSTOFMONTH,EOMONTH(B2,-1)+1, LASTOFTHEMONTH,EOMONTH(B2,0), DAYSINTHEMONTH,LASTOFTHEMONTH - FIRSTOFMONTH+1, WEEKDAYFIRSTOFMONTH,WEEKDAY(FIRSTOFMONTH,1), NBRBLANKSFIRSTOFMONTH,7-WEEKDAYFIRSTOFMONTH, REMAININGDAYSAFTERFIRSTWEEK,DAYSINTHEMONTH - WEEKDAYFIRSTOFMONTH, FIRSTWEEKBLANKS,CHOOSE(SEQUENCE(1,NBRBLANKSFIRSTOFMONTH,1,1),"","","","","",""), FIRSTWEEKDAYS,SEQUENCE(1,WEEKDAYFIRSTOFMONTH,1,1), LINE1, IF(WEEKDAYFIRSTOFMONTH = 7, FIRSTWEEKDAYS, HSTACK(FIRSTWEEKBLANKS,FIRSTWEEKDAYS)), NBRFULLWEEKS,INT(REMAININGDAYSAFTERFIRSTWEEK/7), DAYSINLASTWEEK,MOD(REMAININGDAYSAFTERFIRSTWEEK,7), NBRBLANKSATMONTHEND,7-DAYSINLASTWEEK, FULLWEEKS,SEQUENCE(NBRFULLWEEKS,7,WEEKDAYFIRSTOFMONTH+ 1,1), BLANKSATMONTHEND,CHOOSE(SEQUENCE(1,NBRBLANKSATMONTHEND,1,1),"","","","","",""), TOPANDMIDDLE,VSTACK(LINE1,FULLWEEKS), IF(DAYSINLASTWEEK=0,TOPANDMIDDLE, VSTACK(TOPANDMIDDLE,HSTACK(SEQUENCE(1,DAYSINLASTWEEK,COUNT(TOPANDMIDDLE)+1,1),BLANKSATMONTHEND))) )
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B2List=CHOICE
 
Upvote 0
awoohaw said:
My solution is complicated compared to others offered, but here is a dynamic sunday to saturday calendar (the drop down in your date cell is for testing):
Some Mr Excel Questions.xlsx
ABCDEFGHI
1
210/1/2022
3
41234567
5891011121314
615161718192021
722232425262728
8293031
9
DYNAMIC CALENDAR
Cell Formulas
RangeFormula
B4:H8B4=LET( FIRSTOFMONTH,EOMONTH(B2,-1)+1, LASTOFTHEMONTH,EOMONTH(B2,0), DAYSINTHEMONTH,LASTOFTHEMONTH - FIRSTOFMONTH+1, WEEKDAYFIRSTOFMONTH,WEEKDAY(FIRSTOFMONTH,1), NBRBLANKSFIRSTOFMONTH,7-WEEKDAYFIRSTOFMONTH, REMAININGDAYSAFTERFIRSTWEEK,DAYSINTHEMONTH - WEEKDAYFIRSTOFMONTH, FIRSTWEEKBLANKS,CHOOSE(SEQUENCE(1,NBRBLANKSFIRSTOFMONTH,1,1),"","","","","",""), FIRSTWEEKDAYS,SEQUENCE(1,WEEKDAYFIRSTOFMONTH,1,1), LINE1, IF(WEEKDAYFIRSTOFMONTH = 7, FIRSTWEEKDAYS, HSTACK(FIRSTWEEKBLANKS,FIRSTWEEKDAYS)), NBRFULLWEEKS,INT(REMAININGDAYSAFTERFIRSTWEEK/7), DAYSINLASTWEEK,MOD(REMAININGDAYSAFTERFIRSTWEEK,7), NBRBLANKSATMONTHEND,7-DAYSINLASTWEEK, FULLWEEKS,SEQUENCE(NBRFULLWEEKS,7,WEEKDAYFIRSTOFMONTH+ 1,1), BLANKSATMONTHEND,CHOOSE(SEQUENCE(1,NBRBLANKSATMONTHEND,1,1),"","","","","",""), TOPANDMIDDLE,VSTACK(LINE1,FULLWEEKS), IF(DAYSINLASTWEEK=0,TOPANDMIDDLE, VSTACK(TOPANDMIDDLE,HSTACK(SEQUENCE(1,DAYSINLASTWEEK,COUNT(TOPANDMIDDLE)+1,1),BLANKSATMONTHEND))) )
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B2List=CHOICE
Click to expand...
I just realized I deleted the range name for the drop down. So please ignore that comment.
 
Upvote 0
If you want to use Excel's relatively new feature named Lambda, I enclose a calendar for month and another for a year.

Calendar for year
Example CalYear(2023) the stated parameter is "Year"; year can be specified like CalYear(2023) or as 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)))

Calendar_Examples.xlsm
ABCDEFG
12023
2SunMonTueWedThursFriSat
31-Jan-232-Jan-233-Jan-234-Jan-235-Jan-236-Jan-237-Jan-23
48-Jan-239-Jan-2310-Jan-2311-Jan-2312-Jan-2313-Jan-2314-Jan-23
515-Jan-2316-Jan-2317-Jan-2318-Jan-2319-Jan-2320-Jan-2321-Jan-23
622-Jan-2323-Jan-2324-Jan-2325-Jan-2326-Jan-2327-Jan-2328-Jan-23
729-Jan-2330-Jan-2331-Jan-231-Feb-232-Feb-233-Feb-234-Feb-23
Calendar (2)
Cell Formulas
RangeFormula
A2:G55A2=CalYear(A1)
Dynamic array formulas.
 
Upvote 0
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(53,7,WORKDAY.INTL(d+1,-1,"1111110"),1),IF(dt-DAY(dt)+1=d,dt,""))))

Calendar_Examples.xlsm
ABCDEFG
111-Jan-23
2SunMonTueWedThursFriSat
31-Jan-232-Jan-233-Jan-234-Jan-235-Jan-236-Jan-237-Jan-23
48-Jan-239-Jan-2310-Jan-2311-Jan-2312-Jan-2313-Jan-2314-Jan-23
515-Jan-2316-Jan-2317-Jan-2318-Jan-2319-Jan-2320-Jan-2321-Jan-23
622-Jan-2323-Jan-2324-Jan-2325-Jan-2326-Jan-2327-Jan-2328-Jan-23
729-Jan-2330-Jan-2331-Jan-23
Calendar (2)
Cell Formulas
RangeFormula
A2:G8A2=CalMonth(A1)
Dynamic array formulas.
 
Upvote 0
Once again, thanks for giving me more credit than I deserve. Believe it or not, I have been working on your original post for a long while, and do not want to overextend my welcome, but if you are able to get me off the ground, much appreciated.

In the January 2023 cell I don't understand =DATE(B1,MONTH(--(1&B2)),1)
As I see it, which could be wrong, b1=year, but MONTH(--(1&B2)) I don't understand. The ,1 at the end I see as the 1st day of month.

I have other questions like what dt and D2 are...variables?
And I assume the last part of the LET formula (the if section), leaves the cell blank if there is no date in it

What part of the formula tells it to start on Sunday?

Thanks for your patience
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Auto update the date range
Replies
3
Views
248
kvsrinivasamurthy
K
J
  • Solved
Pull values from For/Next statement?
Replies
3
Views
92
jmpatrick
J
J
  • Question Question
Filling backcolor cells color based on the color of a cell
2 3
Replies
22
Views
537
jgalas
J
A
  • Question Question
Creating a Calendar for a Month with tasks
Replies
0
Views
479
anuradhagrewal
A
G
  • Question Question
Call number of hours in Project Plan Gantt Chart Template
Replies
4
Views
46
GreenLanternX
G

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top