Forum Discussion

ld's avatar
ld
Copper Contributor
Jun 15, 2022
Solved

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...
excel
Formulas and Functions
office 365
  • mtarler's avatar
    mtarler
    Jun 15, 2022

    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

     

mtarler's avatar
mtarler
Silver Contributor
Jun 30, 2023
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.
Ghazal's avatar
Ghazal
Copper Contributor
Jul 11, 2023

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 

  • Ghazal's avatar
    Ghazal
    Copper Contributor
    Jul 11, 2023

    mtarler 

    Thank You so much for your help.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 11, 2023

    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.