How to Link Excel Sheets by Date to Create a Calendar Task View
Question details
The user wants to automatically sync tasks from a data sheet into a visual monthly calendar sheet using dates, applying conditional formatting to highlight active task days.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating an automated project tracker where tasks and date ranges entered in a list automatically populate and format a monthly calendar view.
- Observed behavior
- Tasks need to automatically display on the correct calendar dates and trigger visual highlights based on start and end dates when new items are added to the tracker.
Ensure your task list has clearly defined 'Start Date' and 'End Date' columns formatted as standard Dates. It is highly recommended to convert your task list range into a formatted Table so that formulas automatically update when new tasks are added.
Use Formulas and Conditional Formatting to Sync Tasks
Use a combination of COUNTIFS for conditional formatting and dynamic array formulas (like FILTER) to automatically display and highlight tasks in your calendar view based on date ranges.
By setting up rules that compare your calendar dates against the start and end dates in your task list, you can create a dynamic calendar that updates visually the moment a new task is entered.
Go to your task sheet, highlight your data including headers (Task Name, Start Date, End Date), and press Ctrl+T to convert it into a Table. Name this table 'TaskTracker' in the Table Design tab.
On a new sheet, create a grid representing your monthly calendar. Ensure the date cells contain actual date values (e.g., 1/1/2024). You can custom format these cells to display only the day number by right-clicking, selecting 'Format Cells', choosing 'Custom', and typing 'd'.
Select the entire calendar grid. Navigate to 'Home' > 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Enter a formula like =COUNTIFS(TaskTracker[Start Date],"<="&A1, TaskTracker[End Date],">="&A1)>0 (assuming A1 is your first calendar date cell). Choose a fill color and click OK.
If you want the actual task names to appear inside the calendar cells, select the cell directly below your date cell and use the FILTER and TEXTJOIN functions to pull data: =TEXTJOIN(", ", TRUE, FILTER(TaskTracker[Task Name], (TaskTracker[Start Date]<=A1)*(TaskTracker[End Date]>=A1), "")).

Create Automated Calendars with WPS Spreadsheet
WPS Office provides robust formula support and intuitive conditional formatting tools, making it incredibly easy to build automated task trackers and dynamic calendar views without heavy software.
- 1. Open your Task Tracker: Launch WPS Spreadsheet and open your existing .xlsx file or create a new workbook with a Task List and Calendar sheet.
- 2. Format as Table: Select your task list, go to the Home tab, and click 'Format as Table' to ensure your data range expands dynamically.
- 3. Add Conditional Formatting: Highlight your calendar dates, go to 'Home' > 'Conditional Formatting' > 'New Rule', and input your COUNTIFS formula to highlight task durations.
- 4. Apply Dynamic Formulas: Use WPS Spreadsheet's built-in FILTER function to pull task names directly into your calendar grid flawlessly.

Frequently Asked Questions
Why is my conditional formatting not highlighting the correct dates in my calendar?
This usually happens if the dates in your task list or calendar are stored as text strings instead of proper Date values. Select the affected cells, right-click, choose 'Format Cells', and ensure they are categorized as 'Date'. Additionally, verify that your formula's cell references (like $A$1 vs A1) are correctly set as relative or absolute.
Will this calendar update automatically if I add new tasks next month?
Yes, provided you converted your original task list into a Table (using Ctrl+T) before setting up your formulas. Tables expand dynamically, meaning your conditional formatting and lookup formulas will automatically include the newly added rows without needing manual range adjustments.
Can I highlight overlapping tasks with different colors?
Yes, but you will need to create multiple conditional formatting rules. For example, you can add a 'Category' column to your task list and create separate COUNTIFS rules for each category, assigning a different fill color for each rule in the Conditional Formatting manager.




