I am creating a yearly calendar that can start from any month as specified by the user. The calendar for each month will show the previous month and the next month as well. I have a list of public holidays, but because the calendar can be started from any month I need to have 3 years of holiday dates, from December the year before the first month (in case the user chooses the start month of January so I can show the previous month when they are looking at the January month) right through to December of the year after the first month (in case the user chooses the start month of December so I can show the following month when they are looking at the November month). This is further complicated by the fact that some of the holidays are variable, so some years they will fall within the period and other years they wont, and some years two holidays will fall on the same date and in other years they will fall on different dates. What I am wanting is to get a unique list of the holiday dates that fall within the period of the 1st of the month prior to the month chosen to start the calendar right through to the end of the month, 13 months after the month chosen to start the calendar. I know how to find the date range using edate and eomonth functions, but I can't work out the best way to pull out only the dates within that range. I was thinking it would probably be an array formula, but I'm not too familiar with those, especially over multiple columns
Once I have the unique list of dates I want to bring into an adjacent cell all of the names of the holidays that occur on that date, preferably with a comma and space separating them, or at least a space separating them (that is why each holiday name has a space at the end of it).
Attached is a draft spreadsheet with the holidays in it, and a basic one month calendar. Any help is appreciated. This is my own creation so I can move things around if necessary.
Bookmarks