Forum Discussion
Fill Excel Calendar from a List
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.
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
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
- 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.
- which template? can you share your workbook (no confidential/personal/private info)?
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?
