Forum Discussion
Fill Excel Calendar from a List
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
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
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)
- We need to be able to put a date range Start Date - End Date.
- This was already created by/for another poster on this thread - see attached
- We need to be able to include the year of the event, if recurring annually be able to plug it in without a year.
- Attached includes the year. Recurring annually is a problem because how do you "plug it in without a year"? if you type "Jan 12" it will assume a date and apply this year. Not saying this isn't possible but not sure on what format/input style to use because entering 'dates' for the date range makes that easy but makes recurring hard. Entering day and month separately makes date (and year) ranges harder to enter. adding another column for repeating is yet another option. just not sure what makes sense here.
- A formula to highlight certain events a certain color.
- Is it easiest to use Conditional formatting or IF-Then formula?
- Formatting can ONLY be done using Conditional Formatting (or manually)
- If event has spans over multiple days or bleeds into the next month, how would that look?
- How would you want that to look? I could see adding conditional formatting rules but this could get complicated. Maybe just a few key 'categories' maybe? In attached I added column for 'category' and conditional rules based on 'family', 'social' and 'business'
- How do we get that range to highlight in one color throughout that timeframe?
- Again not sure what you're thinking and I would highlight the specific event not the whole day so overlapping events don't cause issues. Example in attached.
- We need to be able to put a date range Start Date - End Date.
mtarler Thank you for all your help with the calendar I just have a few more questions/request.
- We need to be able to put a date range Start Date - End Date.
- We need to be able to include the year of the event, if recurring annually be able to plug it in without a year.
- A formula to highlight certain events a certain color.
- Is it easiest to use Conditional formatting or IF-Then formula?
- If event has spans over multiple days or bleeds into the next month, how would that look?
- How do we get that range to highlight in one color throughout that timeframe?
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.
- 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.
