Organizing Project Dates in a Custom Google Sheets Calendar

Other Questions Google Sheets
1

I’m working on a Google Sheets calendar for my projects. I’ve got two sheets: ‘Projects’ with all the project info and dates (some projects last up to two weeks) and ‘Schedule’ with the actual calendar.

I want to show the project name on each day it’s booked in the Schedule sheet. Right now, I can only get it to work for the first day of each project.

Here’s what I’ve tried:

=IFERROR(QUERY(Projects!$A$3:$P,
  "select A where C = date '" & TEXT(D4, "yyyy-mm-dd") & 
  "' or D = date '" & TEXT(D4, "yyyy-mm-dd") & "'", 1), "")

This formula checks if the date in D4 of the Schedule sheet matches any date in columns C or D of the Projects sheet. If it does, it returns the project name from column A.

How can I make this work for all the project dates, not just the first one? Any ideas would be super helpful!

2

hey grace, sounds like a cool project! have u tried using ARRAYFORMULA? it might help u apply the formula to all cells at once. also, u could use FILTER instead of QUERY for simpler logic. just an idea, hope it helps! let me know if u need more details :slight_smile:

3

I’ve tackled a similar challenge in my work, and I found that using a combination of FILTER and SEQUENCE functions can be quite effective. Here’s an approach that might work for you:

=ARRAYFORMULA(
IFERROR(
FILTER(
Projects!$A$3:$A,
D4 >= Projects!$C$3:$C,
D4 <= Projects!$D$3:$D
),
“”
)
)

This formula checks if the date in D4 falls within any project’s date range. It should populate all cells with the appropriate project name for its duration. The ARRAYFORMULA allows it to be dragged across your calendar.

One caveat: If projects overlap, you might see multiple names in a cell. You could wrap it in a JOIN function to display them neatly. Let me know if you need any clarification on implementing this!

4

I’ve encountered a similar issue when managing project schedules. One solution that worked well for me was using a combination of FILTER and ARRAYFORMULA functions. Here’s a formula you could try:

=ARRAYFORMULA(TEXTJOIN(", ", TRUE, FILTER(Projects!$A$3:$A, D4>=Projects!$C$3:$C, D4<=Projects!$D$3:$D)))

This formula will check each date in your Schedule sheet against the project date ranges in the Projects sheet. It returns all project names that fall within the date range, joining multiple projects with a comma if there’s overlap.

Remember to adjust the cell references to match your specific sheet layout. This approach should populate your entire calendar automatically once you’ve entered it in the first cell. Let me know if you need any clarification on implementing this solution.