Arranging Dates in a Custom Calendar with Google Sheets

Other Questions Google Sheets
1

I’m working on a calendar in Google Sheets and I need some assistance with the setup. I have a sheet named ‘Projects’ that lists all my projects along with their respective dates, ranging from a single date to spans of up to 14 continuous days. Additionally, I have another sheet titled ‘Calendar’ where I want to filter and display the booked dates. My goal is to show the project name for each specific day in the calendar where it is listed in the ‘Projects’ sheet.

Here’s the formula I have attempted that almost works:

=IFERROR(QUERY(Projects!$A$3:$V, "select A where E = date '"&TEXT(H4,"yyyy-mm-dd")&"' or F = date '"&TEXT(H4,"yyyy-mm-dd")&"' or G = date '"&TEXT(H4,"yyyy-mm-dd")&"' or H = date '"&TEXT(H4,"yyyy-mm-dd")&"' or I = date '"&TEXT(H4,"yyyy-mm-dd")&"' or J = date '"&TEXT(H4,"yyyy-mm-dd")&"' or K = date '"&TEXT(H4,"yyyy-mm-dd")&"' or L = date '"&TEXT(H4,"yyyy-mm-dd")&"' or M = date '"&TEXT(H4,"yyyy-mm-dd")&"' or N = date '"&TEXT(H4,"yyyy-mm-dd")&"' or O = date '"&TEXT(H4,"yyyy-mm-dd")&"' or P = date '"&TEXT(H4,"yyyy-mm-dd")&"' or Q = date '"&TEXT(H4,"yyyy-mm-dd")&"' or R = date '"&TEXT(H4,"yyyy-mm-dd")&"' or S = date '"&TEXT(H4,"yyyy-mm-dd")&"' or T = date '"&TEXT(H4,"yyyy-mm-dd")&"' or U = date '"&TEXT(H4,"yyyy-mm-dd")&"' or V = date '"&TEXT(H4,"yyyy-mm-dd")&"'", 1), "")

Essentially, I would like to update the Calendar to reflect all entries from Column A in the Projects sheet that correspond to the dates on the Calendar. Currently, the formula only retrieves the project’s starting date and not subsequent days. Assistance or suggestions would be greatly appreciated!

2

I’ve worked on a similar calendar in Sheets and found that using array formulas can simplify this kind of task quite a bit. You might want to consider restructuring your Projects sheet so that each project spans across rows rather than columns. Each row should represent a day, therefore allowing you to have a simple structure of ‘Project Name - Date’. This way, with a VLOOKUP or FILTER formula, you can dynamically pull in the relevant data for each date into your Calendar sheet. Essentially, this will make querying the associated dates much simpler and more efficient, allowing you to avoid the lengthy formula context you’ve built so far. This change in structure can initially seem cumbersome but makes everything much easier to scale and manage in the long run.

3

You can also leverage apps script to automate this task. A script can search for matching dates and fill the calendar accordingly. It’s a bit advanced at first, but once set up, it’ll easily handle the complexities of multiple date ranges without cluttering your sheets with long formulas. Give it a try!