New Duration Dates in Project Plan

I've been working on updating an existing project plan to add additional fields for reporting duration. I want to keep the existing Duration column and it's formulas that uses dependencies on the Predecessors and the Duration column.

For the new data, the additional info is pulled from the original start date and the last date in a range when the status is Complete by using a MAX/COLLECT formula. I was able to get that to work properly using Helper columns. To calculate a the duration, I can use the =NETWORKDAYS([Helper Start Date]@row, [Helper Complete Date]@row) formula but do I need to create another sheet for holidays? Or would I be able to use an additional Helper Duration column and a Helper Predecessor column (if possible) and set the dependencies that way? Ideally, I don't want to create all these helper columns and sheets if I can get it to work in one column. I'm sure that I'm missing something but any changes I've made don't achieve my expectations.

I've attached a screenshot of my sample project plan. Any assistance you could provide would be greatly appreciated!


Answers

  • Ipshita
    Ipshita ✭✭✭✭✭✭

    Hi @Lisa Townsend do you have your project settings turned on in this sheet? That could be very helpful as you can define your durations 1 and 2 here and you can set up your holidays inside of the project settings.

    Hope this helps.

    Cheers,

    Ipshita

    Ipshita Mukherjee

  • Hi @Ipshita. Yes, I have the project settings turned on for the sheet. I need to keep one duration column with the project settings turned on. I need another column for just the duration between 2 days. However after speaking with my co-workers on the criteria needed for the duration columns, it appear that holidays do not need to be subtracted from the overall calculation. I believe I have found a workaround for my issue.

    If I find that the criteria changes (as it often does), I will set up my holidays through the project settings.

    Thank you for sharing your knowledge.

    Lisa

  • Ipshita
    Ipshita ✭✭✭✭✭✭

    @Lisa Townsend - I am glad it worked out for you!

    Have a wonderful day!

    Cheers,

    Ipshita

    Ipshita Mukherjee

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!