Function Problems

How to Link Excel Sheets by Date to Create a Calendar Task View

Guest WriterGuest Writer Oct 9, 2026 870 views

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.

How to Link Excel Sheets by Date to Create a Calendar Task View
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.
Before you start

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.

Solution 1Recommended

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.

1
Format the Task Table

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.

2
Set Up the Calendar Grid

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'.

3
Apply Conditional Formatting

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.

4
Display Task Names on the Calendar

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), "")).

Use Formulas and Conditional Formatting to Sync Tasks
Formula Compatibility: Functions like FILTER and TEXTJOIN are available in newer spreadsheet versions. If using an older version, you may need to rely on complex array formulas using INDEX, MATCH, and IF, or stick purely to conditional formatting.
Advanced Spreadsheets Made Easy

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. 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. 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. 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. 4. Apply Dynamic Formulas: Use WPS Spreadsheet's built-in FILTER function to pull task names directly into your calendar grid flawlessly.
Fully compatible with Microsoft Excel (.xlsx) formats, ensuring your formulas and formatting work seamlessly.Built-in support for advanced dynamic array functions like FILTER, XLOOKUP, and TEXTJOIN.An easy-to-navigate Conditional Formatting rules manager to highlight dates quickly.Free, lightweight, and works efficiently even with complex data sets.
microsoft office alternative - wps office

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.