How to Display Full Event Durations in a Dynamic Excel Calendar
Question details
The user needs to modify a dynamic Excel calendar so that events spanning multiple days are displayed on every date they cover, rather than just their start date.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Building or updating a dynamic calendar to track multi-day projects, holidays, or continuous events.
- Observed behavior
- Events currently display only on their start date instead of spanning the full duration across all applicable dates.
Ensure your event data table is properly structured with separate columns for 'Event Name', 'Start Date', and 'End Date', and that all dates are formatted correctly as date values.
Use FILTER and TEXTJOIN to Display Multi-Day Events
By combining the TEXTJOIN and FILTER functions, you can check if a calendar date falls between an event's start and end dates, returning the event name for all applicable days.
To make an event appear on multiple days, the calendar formula must evaluate a date range rather than looking for an exact match. The FILTER function can test two conditions simultaneously: whether the calendar date is greater than or equal to the start date, and whether it is less than or equal to the end date.
Create a data table for your events with three columns: Event Name, Start Date, and End Date. For easier referencing, format this range as a Table and name it 'Events'.
Navigate to your dynamic calendar grid and click on the cell where the event text for the very first date should appear.
Type the formula: =TEXTJOIN(", ", TRUE, FILTER(Events[Event Name], (A2>=Events[Start Date])*(A2<=Events[End Date]), "")). Replace 'A2' with the cell reference that contains the specific calendar date. The multiplication symbol (*) acts as an 'AND' operator, ensuring both date conditions are met.
Press Enter to apply the formula. Then, drag the fill handle across your calendar grid to copy the formula to all other date cells. Multi-day events will now populate on every date they cover.
Create Dynamic Calendars with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and text manipulation functions like TEXTJOIN, making it effortless to build and manage dynamic multi-day event calendars.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dynamic calendar.
- 2. Input the event formula: Select the event display cell and enter the TEXTJOIN and FILTER combination formula.
- 3. Fill the calendar grid: Drag the formula across your calendar layout to instantly display all multi-day events correctly.
Frequently Asked Questions
Why does my FILTER function return a #NAME? error?
The #NAME? error typically occurs if you are using an older version of spreadsheet software that does not support dynamic array functions like FILTER. Ensure you are using a modern version like WPS Office or Excel 365. For older versions, an INDEX and AGGREGATE combination is required.
How do I put multiple events on separate lines within the same cell?
You can change the delimiter in the TEXTJOIN function to a line break. Instead of using ", " as the first argument, use CHAR(10). Remember to enable 'Wrap Text' on those calendar cells so the line breaks display properly.
Can I highlight days that have an event automatically?
Yes. You can use Conditional Formatting by creating a new rule with the formula =LEN(B2)>0 (assuming B2 is your event text cell). Apply a fill color to highlight any calendar day where an event is populated.
