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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭

    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

    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • @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:


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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.

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!