Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

dynamic excel calendar help pls

Brass Contributor


I saw a youtube to create a dynamic calendar


the dates are using sequence, may I know how to have column D for text input 

Mon to Sun same for the rest of the columns

I want to have one column as the date and the other for input text
the calendar to dates to move accordingly,  I cannot input a column the date doesnt move..

Please advise. 

col C8 -=SEQUENCE(6,7,0)+DATE($C$4,$K$2,1)-WEEKDAY(DATE($C$4,$K$2,1),2)+1



to be like that


Thank you



9 Replies


I modified the formula so that you can input a text beside each date.

Screenshot 2023-12-15 at 5.22.40 PM.png

hi Rachel

Thank you is there any way to auto populate the text from any other tab that match the date in the calendar?

to display eg Title, Category , Start to end date / time.

Attached sample tab , event data start and end time are from SP which contain Date and Time, but how to use Text Join to match it to the cal date and display across if it run few day?  Thank you





Yes, you can use Filter to put event next to date which is greater than start date and less than end date:

Screenshot 2023-12-15 at 5.54.12 PM.png


hi Rachel,

Thank you but what is HSTrack?

I notice that u Run the event A to F, if I want to pick from selected columns, not all the columns Text, how to as the real data is very big.   Thank you

Thank you



HSTACK stacks columns horizontally.

e.g if you want to include column A and column F, you can use


if you want to include column A, B and F, you can use

hi Rachel,
Great it works.


. One last question, the text are display as a string of text, is there anyway to break it into next row
event -bold title
add Status :  the data fr the list?

Also anyway to color format the item by it Category?  Thanks

Thank you



I think you will have to change the "Dynamic Calendar" if you want to break event information into different rows.

I attached an example.


As for colouring, you can use conditional formatting to do that:

Screenshot 2023-12-15 at 10.05.06 PM.png



I have tried, but it's not working
How to link it back to the year and month to pull the data, do I still need to use TEXT join?




I added Named Range for Year and Month and updated the formula too:

Screenshot 2023-12-18 at 4.52.25 PM.png


You don't need TEXTJOIN now. as Event information are displayed in 3 rows using TRANSPOSE.


Screenshot 2023-12-18 at 4.55.48 PM.png