Fill Excel Calendar from a List

[ld]

I have a list in excel of monthly recurring events. Column A contains the event name and column B is the day of the month that it occurs (1-31). Some days have multiple events. I would like to create a calendar from this list that will automatically update as new events are added to it, that way we are not re-entering it every month. Is there a formula I can put in one of the monthly calendar templates that will accomplish this? I understand it can be accomplished with recurring appointments in Outlook but we are looking for a different format. 

Event	Day of Month
Event A	4
Event B	9
Event C	15
Event D	9

I'd like the end result to show "Event A" on the 4th of every month, "Event B" and "Event D" on the 9th of every month, and "Event C" on the 15th of every month. I'd like to use the "Any year one-month calendar" template.

[#2 · Accepted solution]

With Excel 365 you can use FILTER()
Lets say you have the cell A1 has the day number in it and a few lines below it for 'events' to be typed then in A2 type:
Assuming you Format as Table the Event list (name the table EVENTS)
=FILTER( EVENTS[Event], EVENTS[Day of Month]=DAY(A1), "")
(if you don't format the event list as table replace EVENTS[Event] with the range of event names and EVENTS[Day of Month] with range of event days, BUT I highly recommend you do format it as a table)

this assumes the days in the template are actual DATE if they are NOT actual date values then replace DAY(A1) with just A1

[#3]

Thank you, this appears to not work though. I may need a different calendar. I believe it is because the dates in the calendar are part of an array? It seems I can't copy the calendar over using paste values to clear the array, it messes the calendar up.

[#4]

which template? can you share your workbook (no confidential/personal/private info)?

[#5]

@mtarler Here is some generic data & the calendar we are trying to use. We're aware that the calendar only has one row per day - I'll be adding rows once we figure out the fill issue. If we need to use a different one that would be fine, we'd just like the ability to switch the month and have the events autofill into the new month.

We're doing it this way because we need a clear visual representation of the events. We'll eventually have to add conditional formatting for a third set of data (less than or greater than highlighting for #'s associated with each event). Your formula worked in a separate sheet, just not with this calendar due to the formulas/array. 

[#6]

@ld  hmmm.  since you formatted the table as I suggested I literally just pasted the formula as is into the cell and change the 1 cell reference from A1 and it worked perfect (except the #spill errors as expected). I went and added 3 more rows per week, fixed the formatting and see attached.

[#7]

Thank you!! I'm not sure why I was getting an error, but this is exactly what we needed! I will have to learn more about the less common formulas and spill errors.

[#8]

How do you format it for single events that do not recur?
Is there a way to see the full 12 month calendar view instead of one month at a time?
How do you format the event list for all 12 months instead of 1 month? For example I could list all the events needed throughout the year in one list so that they automatically populate on the calendar view?

[#9]

@-needhelp what you want sounds very different that what this thread wanted. Maybe create a new thread with your actual question and needs. I would also suggest searching for excel templates for something close to what you want to help you a) explain what you want and b) give the volunteers here something to start from.

EDIT - nevermind the first part, I see you did create a new post.  As for the second part, it would help if you found a year calendar that is close to what you want and what you mean/want/need in terms of repeating vs non-repeating events....

[#10]

How do you change the calendar to input data based on the month and day you enter? Not reoccurring events. I would like to add an event and it post on the day and month I input in the events.

[#11]

@ramonm76  I updated the template to allow for a month to be added and if no month then it will repeat every month.  See attached.

[#12]

@mtarlerI tried it and it did not populate any data. All I did was change the event to see if it worked. All I need is to add an event for a specific month and it will populate.  On that day and month when I input it. thank you for your time and help

[#13]

@ramonm76  I don't understand what you did and what didn't work.  Here is an image of the event entry page:

notice how events A-L are given both a day AND a month while events M-Y only have a Day.  Now look at the Calendar tab and you see:

and you see all the events M-Y and only event I on the 13th from events A-L because only event I was on month 9 (September)

[#14]

@mtarler This is what I see when I download the Excel document. I did not change anything. It is not populating the information that you have here. 

[#15]

It would appear you are not using Excel 365? and these worksheets use functions that require 365 (or at least 2019 I think). Something similar may be possible using older functions but I have no way to verify it.

[#16]

Hi,,,I have a dynamic calendar with auto populated multiple events of same date using filter function.

How i can hide the event of previous month?

[#17]

@mtarler 

I have a dynamic calendar with auto populated multiple events of same date using filter function.
How i can hide the event of previous month?

https://a1office.co/solutions/shared/view?fileKey=Sample-0517d84902c095b3db5bf0d78fd089ce.xlsx 

[#18]

@Ghazal So an easy answer to your question is you can 'and' the filter with the month() of any day like this:

=FILTER(Events!$A:$A,(Events!$B:$B=Calendar!B5)*(MONTH(B5)=MONTH($B$11)),"")

so I picked cell $B$11 as a day that is guaranteed to be in this month.  That said I noticed a couple other things and suggest the following formula:

=LET(E, 
FILTER(Events[Events],(Events[From Date]<=B11)*(Events[To Date]>=B11)*(MONTH(B11)=MONTH($B$11)),""), 
IF(ROWS(E)<=5, E, VSTACK(TAKE(E,4),TEXTJOIN(", ",,DROP(E,4)))))

so I did 3 things in this version:

a) TABLE references - so I renamed the table on the Events TAB to be called Events and used references to columns in that table: Events[Events], Events[From Date], and Events[To Date].  This is 'better' because it doesn't have Excel looking at a column of a million blanks and it is clear in the formula what you are looking at.

b) Date Range - so I noticed the events have a start and end date so this formula will show that event on every day it is going on instead of only the day it starts

c) SPILL - so if you have more than 5 events listed for a day that would cause a problem so in this formula I show the first 4 and then the 5th cell will add all the other events in a list style.

obviously you may like or not like these additions and may have alternative preferences but at least this gets you some ideas.

[#19]

@mtarler 

Thank You so much for your help.

[#20]

@mtarler Thank you for all your help with the calendar I just have a few more questions/request. 

1. We need to be able to put a date range Start Date - End Date.
2. We need to be able to include the year of the event, if recurring annually be able to plug it in without a year.
3. A formula to highlight certain events a certain color.
1. Is it easiest to use Conditional formatting or IF-Then formula?
2. If event has spans over multiple days or bleeds into the next month, how would that look?
3. How do we get that range to highlight in one color throughout that timeframe?