How to make a master calendar with some showing full duration, and others only the end date
Here is a picture of my calendar currently. I am trying to make a master calendar where I add due dates for projects into this calendar. The issue I am running into, is that since these are long projects, I don't want the bar to show the whole duration, just the end date. However, for the vacation/travel, I want it to show the whole duration. I tried using a report, but wasn't able to make only the end date show for just some of my dates. Any help would be appreciated!
Best Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Alicia D
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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.
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
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
-
Hi @Alicia D
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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:
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
Hi @Alicia D
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
Hi @Alicia D
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Yes, thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!