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

hi

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

bbsin_3-1702628427239.png

 


to be like that

bbsin_2-1702628388413.png



Thank you

 

 

9 Replies

@bbsin 

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

bbsin_0-1702633393687.png

 

@bbsin 

 

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

@rachel 

hi Rachel,

Thank you but what is HSTrack?
Capture.JPG

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

@bbsin 

 

HSTACK stacks columns horizontally.

e.g if you want to include column A and column F, you can use
=HSTACK($A:$A,$F:$F)

 

if you want to include column A, B and F, you can use
=HSTACK($A:$B,$F:$F)

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
confirmed
XXX
add Status :  the data fr the list?

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

Thank you

@bbsin 

 

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

@rachel 

 

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?

 

Capture.JPG

@bbsin 

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