Forum Discussion
Create autopoulating excel calendar with colour coded categories
You'd have to insert enough rows under each date row to accommodate the maximum number of events per day that you expect. Let's say that you won't have more than 8 events per day. You currently have 1 row for each day, so you'd have to insert 7 new rows under each day row.
You can then omit TEXTJOIN from the formulas:
=IFERROR(TEXTJOIN(REPT(CHAR(10),1),TRUE,FILTER(Schedule[[Title]:[Title]],(Schedule[[End Date]:[End Date]]=(DATEVALUE(B6&"-"&MoMonth&"-"&MoYear))),"-")),"")
becomes
=FILTER(Schedule[[Title]:[Title]],Schedule[[End Date]:[End Date]]=(DATEVALUE(B6&"-"&MoMonth&"-"&MoYear)),"-")
This formula will automatically spill to as many cells as needed.
Because there is now only one category per cell, you can apply conditional formatting rules - one rule for each category.
Thanks for looking into this - I’m stuck. No idea how to add category colour coding!
https://www.dropbox.com/scl/fi/3stl3pl7pnt4v4ziht8lj/Planner-template.xlsx?rlkey=kedandd3f12nmxp97pl5f0cdi&dl=0
Thanks. What you want is not possible - the Month View - All categories and Yearly View - All categories sheets contain formulas that combine events from multiple categories in a single cell.
Excel does not support colouring part of the result of a formula, so we cannot colour - say - a Category 1 event red and a Category 2 event green.
- Do you know how I could change the template/formulas so the events come through in separate cells under each date? If that was possible would colour coding then work?
You'd have to insert enough rows under each date row to accommodate the maximum number of events per day that you expect. Let's say that you won't have more than 8 events per day. You currently have 1 row for each day, so you'd have to insert 7 new rows under each day row.
You can then omit TEXTJOIN from the formulas:
=IFERROR(TEXTJOIN(REPT(CHAR(10),1),TRUE,FILTER(Schedule[[Title]:[Title]],(Schedule[[End Date]:[End Date]]=(DATEVALUE(B6&"-"&MoMonth&"-"&MoYear))),"-")),"")
becomes
=FILTER(Schedule[[Title]:[Title]],Schedule[[End Date]:[End Date]]=(DATEVALUE(B6&"-"&MoMonth&"-"&MoYear)),"-")
This formula will automatically spill to as many cells as needed.
Because there is now only one category per cell, you can apply conditional formatting rules - one rule for each category.
- Thank you - this worked for me!!
