Forum Discussion
Solved
Community event planner calendar template
Hello,
I am creating a national training calendar and wanted to use the "Community event planner" template excel offers. This template is ideal becuase once I add the events to the table they populate in the calendar template under the correct date. The only issue that I am having is that some of our events have an end date. Some of them can last up to two weeks. I added an end date column to the table so I can maybe add more to the formula but then when I tried it I couldn't figure it out. Below is the formula that is in the tab and the image attached is an example of the outlook view I am trying to duplicate. That is how I want the excel calendar to look. Any insight on this would be greatly appreciated.
=IFERROR(INDEX(tblEvents,SMALL(IF(Dates=R$6,ROW(Dates)),ROW(2:2))-TableRowStart,4),"")
Hi Ana,
Template compares for each calendar date if such date is in Dates range which is actually the DATE column in the event table.
What you need to if to compare if calendar date is between Dates and EndDates. For that add named range ENDDATE which is EndDate column of the event table and use array formula
=IFERROR(INDEX(tblEvents,SMALL(IF((Dates<=L$11)*(ENDDATE>=L$11),ROW(Dates)),ROW(1:1))-TableRowStart,4),"")
The only difference with initial one is using of
(Dates<=L$11)*(ENDDATE>=L$11) instead of (Dates=L$11)
After that formula is to be copied on each empty cell within calendar date block and for all dates for the same week.
For other week the formula is to be adjusted (e.g you have to change L$11 on L$6 for upper date).
Same way for entire calendar.
In the attached file i did that for few days, not for all of them.
Hi Ana,
Template compares for each calendar date if such date is in Dates range which is actually the DATE column in the event table.
What you need to if to compare if calendar date is between Dates and EndDates. For that add named range ENDDATE which is EndDate column of the event table and use array formula
=IFERROR(INDEX(tblEvents,SMALL(IF((Dates<=L$11)*(ENDDATE>=L$11),ROW(Dates)),ROW(1:1))-TableRowStart,4),"")
The only difference with initial one is using of
(Dates<=L$11)*(ENDDATE>=L$11) instead of (Dates=L$11)
After that formula is to be copied on each empty cell within calendar date block and for all dates for the same week.
For other week the formula is to be adjusted (e.g you have to change L$11 on L$6 for upper date).
Same way for entire calendar.
In the attached file i did that for few days, not for all of them.
Thank you so much!!
Is there anyway to add a formula that will merge those cells with a start and end date?
