Forum Discussion
Task Calendar
I am using a Holiday Template and trying to adapt it to a task calendar. The calendars are the 12 months on their own tabs and can be modified to display any time frame. There is also a Holidays tab that contains a list of holidays (with date and task information that you can choose to display on the appropriate calendar day or not). It is working except that I have multiple date entries on the Holiday tab and I would like each of them to display. Here is the formula for showing the Holidays on each calendar square: =IFERROR(IF(ShowHolidays,IF(INDEX(Holidays,MATCH(DATE(CalYear,MONTH(G9),DAY(G9)),IF(ShowObserved,Holidays[CORRECTED],Holidays[DAY]),0),6),INDEX(Holidays,MATCH(DATE(CalYear,MONTH(G9),DAY(G9)),IF(ShowObserved,Holidays[CORRECTED],Holidays[DAY]),0),2),""),""),"")
Can anyone help me adjust this so that all items on the Holidays list with a date are shown on the calendar?
I am very new to this kind of thing...thanks for the help!
Hi
I am not sure that you can do this simply by formula - I would recommend writing a VBA routine but.....
If you want to try the formula approach I would suggest that you look to using the FILTER command which allows you to filter the date in your holiday table. For example 29th June 2020 has 3 entries; you could then concatenate the entries together. Unfortunately this will probably make your formula even more complicated than it is already!
To write VBA you will need to invest a bit of time to getting it working!
BTW - I would recommend you sort your holiday table in date order.
Hope this helps and good luck!
Peter
3 Replies
Hi
I am not sure that you can do this simply by formula - I would recommend writing a VBA routine but.....
If you want to try the formula approach I would suggest that you look to using the FILTER command which allows you to filter the date in your holiday table. For example 29th June 2020 has 3 entries; you could then concatenate the entries together. Unfortunately this will probably make your formula even more complicated than it is already!
To write VBA you will need to invest a bit of time to getting it working!
BTW - I would recommend you sort your holiday table in date order.
Hope this helps and good luck!
Peter
peteryac60Thank you. I have since organized my "holidays", glad to know I am on the right track there. I will give filter a try and if I need to move to VBA, will spend some time there. Thanks again.
Glad to hear you are moving in the right direction!
I'd be grateful if you would mark the solution as complete!
many thanks,
Peter
