Baseline/Actual Schedule formula help

Nick Monroe

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.

Andrée Starå

@Nick Monroe I shared an example with you just now.

Let me know if it would work or if you have any questions!

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, andree@getdone.se)

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Nick Monroe

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!

Smartsheet help.pdf

Smartsheet help.pdf

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?

Nick Monroe

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å

@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?

Nick Monroe

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å

@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, andree@getdone.se)

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?

Nick Monroe

That will work then

Andrée Starå

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

Andrée Starå

@Nick Monroe I shared an example with you just now.

Let me know if it would work or if you have any questions!

Nick Monroe

Thank you for your help!

Andrée Starå

Happy to help!

Did it work for you?