How to make a master calendar with some showing full duration, and others only the end date

Hey @Alicia D

It looks like we're just missing a closing parentheses to round out the CONTAINS function 🙂

=IF(CONTAINS("Project", [Helper Column 2]@row), [Start Date]@row, [End Date]@row)

Then you only need one at the end. However if you're looking now for project dates instead of vacation dates, you'll also want to swap around the Date that you want the formula to produce:

=IF(CONTAINS("Project", [Helper Column 2]@row), [End Date]@row, [Start Date]@row)

Cheers,

Genevieve

Yes, you've got it! 🙂

The helper end date columns can be hidden in the sheets, but then used in place of the actual end date column in the Report (don't even show the real end date). That way you can display the calendar in the format you'd like.

In regards to the "Project" or not, I didn't understand that your Project versus Vacation are separate sheets. This actually makes things easier! The IF statement formula was assuming there was a mix of Vacation and Projects all in the same sheet, with some identifier (such as text in the task name) to let you know which was which.

With individual sheets, you can get rid of your helper "Project" column because we can simply hard-code the formula based on what sheet it's in.

For Project Sheets:

=[End Date]@row

For Vacation Sheets:

=[Start Date]@row

Then you can make this a column formula so as new rows are added in each sheet, the new data will automatically populate. Does that make sense?

Cheers,

Genevieve

All comments

Monique Odom Stearn

Hello @Alicia D,

Is your calendar based off of a report that points to multiple sheets, or is it all on a single sheet? My formula recommendations will determine if we need to add extra criteria or not.

It pulls from multiple sheets. 10 to be exact! 2 are for time off, and 8 are for projects within the team. @Monique_Odom_Comcast

What I would do in this instance is have a hidden, helper date column with a formula in it in each of your sheets. This column will return one of two options: if the row/task IS Vacation, repeat the Start Date. Otherwise (if it is NOT a Vacation row), repeat the End Date.

Something like this:

=IF(CONTAINS("Vacation", [Task Column]@row), [Start Date]@row, [End Date]@row)

It will need to be a Date column, and you can make this a Column Formula. Then you can surface this hidden column in your Report instead of the actual Start Date column for your calendar.

Cheers,

Genevieve

@Genevieve P. Most of the sheets are for the projects and not the vacation dates. So I made a helper column that I will hide that has the word "project" since it is not always in the task column. I tried this formula: =IF(CONTAINS("Project", [Helper Column 2]@row, [Start Date]@row, [End Date]@row)) and it did not work. Here is a screenshot:

Screen Shot 2024-01-16 at 8.41.27 AM.png

Hey @Alicia D

It looks like we're just missing a closing parentheses to round out the CONTAINS function 🙂

=IF(CONTAINS("Project", [Helper Column 2]@row), [Start Date]@row, [End Date]@row)

Then you only need one at the end. However if you're looking now for project dates instead of vacation dates, you'll also want to swap around the Date that you want the formula to produce:

=IF(CONTAINS("Project", [Helper Column 2]@row), [End Date]@row, [Start Date]@row)

Cheers,

Genevieve

@Genevieve P. amazing, THANK YOU!!! I have been trying to figure this out for weeks! Do you know how I can get the project text to automatically update anytime a new row is created rather than having people manually enter it? Especially since I want it hidden.

How are you determining what's a project and what's a vacation row? Can you post a screen capture of the grid of the sheet with example entries, but block out sensitive data

Really, there are just 8 sheets with nothing but projects, and 2 with nothing but time off. @Genevieve P.

I guess really, this still brings the question of: when I try to create a master report where just the end dates show from the projects, while the start AND end dates are there for the vacation time, how would I make it so that even when it pulls from the helper column end date on the project sheets, how will it be able to pull the start and end date for the time off sheets if I am making a report? @Genevieve P.

I figured it out! I did just end date helped columns on the project sheets and then start & end date helper columns on time off sheets. Thanks for the help!

Yes, you've got it! 🙂

With individual sheets, you can get rid of your helper "Project" column because we can simply hard-code the formula based on what sheet it's in.

For Project Sheets:

=[End Date]@row

For Vacation Sheets:

=[Start Date]@row

Then you can make this a column formula so as new rows are added in each sheet, the new data will automatically populate. Does that make sense?

Cheers,

Genevieve