Baseline/Actual Schedule formula help

Hello all,

I'm looking for some help with formulas in my schedule. With the schedule templet, I can insert the dates into the "start" and "finish" columns, the cells will auto populate. I would like to add two additional rows with "Actual Start" and "Actual Finish". When I enter dates into those rows, I'm looking for them to also auto populate the cells, but in the color red. I'd like for actual start/ actual finish rows to color over the "start"/"finish" column if there is ever an instance when they are different. I am able to do it in excel with conditional formatting, but am new to smartsheet and was wondering if it is possible to do.


Thanks in advance for all of your help.

Best Answer

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Nick,

    Not sure I follow! There's Conditional Formatting in Smartsheet as well so I think we can replicate what you had in excel.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Have a fantastic weekend & Happy Holidays!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hey Andree,

    Thanks for the quick reply! I've forwarded some screen shots to your email and also uploaded them here which can hopefully help clarify the question I was asking. Thank you for all of your help!


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Thanks!

    Are you using dependencies?

    There are two ways to set it up. Here's the first one (the second one will not work if you use dependencies).

    Conditional Formatting that change the bar to Blue when theres a date in the Target Start and change it to red when theres a date in the Actual Start. This would not change the date range in the Gantt view.

    Would that work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I'm using a schedule templet from Smartsheet that auto populates the dates, so I havent used any conditional formatting or dependencies yet. The templet automatically populates the Gantt view when I enter dates in the Start/Finish columns. The main thing I'm trying to do is mimic that feature into the two additional rows I added, I'm not just sure how to go about it.

    Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nick Monroe Should the Gantt view reflect the dates from the actual section if there are any dates in those fields, or do you still want to show the target dates and only change the color?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andree - To help better explain, take a look at the second screenshot in the PDF. We currently have our baseline schedule in the Target start and target finish columns in my excel sheet. That auto populates the gantt view with blue cells. When an activity actually starts and finishes, I put the dates in the actual start and actual finish rows. This auto populates the gantt view with red cells. This is gives us a visible representation on if we started before schedule, on time, or after when we anticipated to start. In the example mentioned, you can see we were supposed to start on 26-feb-20 but we actually started on 28-feb-20. You see there are still the two blue boxes shown. It's an easy way for us to represent to our client if we are on track or behind schedule.

    So when I enter a date in the actual start and actual finish rows in Smartsheet, I would like for it to auto populate the gantt view with red cells. No matter if there is already blue cells in there from the "start" or "finish" rows. I hope that helps to make more sense. I appreciate all of your responses. 

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nick Monroe ok. Then we'll need to use so-called helper columns.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nick Monroe I reread your post, and unfortunately, it won't work as you want it to.

    It's not possible o show two date lengths on the same row. We would need to have to rows, one for the Target and one for the Actual.

    Would that work, or do you want to structure it differently?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • That will work then

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nick Monroe Ok. I'll share something soon!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thank you for your help!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Did it work for you?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Help Article Resources