date constraints in Smartsheet

edited 12/09/19 in Smartsheet Basics

I am looking for a method for creating date constraints in smartsheet (I am assuming that there is not built in functionality)

  • As Soon As Possible
  • As Late As Possible
  • Finish No Earlier Than
  • Finish No Later Than
  • Must Start On
  • Must Finish On
  • Start No Earlier Than
  • Start No Later Than

Has anyone a solution to this?




  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you describe your process in more detail?


    What would be the result? How would it work? What is the logic behind each?

  • Ryan Gerding

    this would be helpful. This is a feature MS Project has. It allows for the earlier of two dates using a hard date instead of using the later of several dates.

  • neil rees

    This is a very basic and fundamental feature to at least be able to set a start or finish on or after constraint so there is a easiest possible date if not being driven by the logic.

    the same applies to a finish constraint but sometime you want to be able to flag if a date has past a point in time so it can be re-planned of mitigated that is when a finish on or before come in.  this gives you critical path(s) i.e. you could have one weekend of cutting in the new servers and one for making them go live but they are usualy fixed point when system down time is allowed.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @neil rees Have you looked into using the Dependencies and Gantt view? It allows you to input predecessors along with lag and lead time as well as milestones which will all populate a Gantt chart where the critical path can be viewed.

    @Ryan Gerding I am not sure I follow exactly what you mean.

  • Philip Martin
    Philip Martin ✭✭
    edited 02/13/20

    @Paul Newcome Dependencies are only a part of solution as to what I understand @CC2019 is asking.

    @Paul Newcome I'm looking to do the same thing mentioned by @CC2019 so let me explain one example that I'm working on right now. Must Finish On (MFO) is a common MS Project function used to make sure a project does not extend past a certain date. If you set an ending task with a start and end date of say 6/1/2020 and a constraint of MFO, the Critical Path is now constrained to that Finish Date.

    Now, if a task that is on the Critical Path were to drift and finish late the program throws a flag and states that you are violating the MFO constraint (or whatever the terminology is). The user must now shorten the Critical Path somewhere else in order to keep the constraint satisfied before the drifted date can be updated. If there is no way that the drift can be taken up in any other task along the Critical Path, the MFO date will then need to moved as a result, but that obviously needs to be reviewed with the Stakeholders so that they are aware of the delay and people will not be happy.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Philip Martin My comment about dependencies were directed more towards Neil instead of CC2019.

    As for the MFO constraint... You could set up an automation to send you an alert when a date is past due, you can set up a flag column with a basic formula in it to flag the row, there are quite a few different options depending on the layout, setup, and use of your sheet(s).

  • Philip Martin

    @Paul Newcome I'll give that a shot, thanks.

  • If I'm understanding this problem correctly, I have the same issue. Scenario: Let's say I have the following tasks: Purchase, Build, Test, Deliver. Let's say that (a.) PURCHASE, BUILD and TEST tasks are all predecessors of DELIVER, and (b.) because of the contract, DELIVER cannot finish any later than 10/1. With MS Project, there were constraints you could set such that MS Project would flag/warn you if any of the predecessors pushed DELIVER beyond the 10/1 date. With Smartsheet, there is no such construct. Here's what I did: In addition to the usual Start and End dates for each row, I also created a column called "Constraint End Date". For PURCHASE, BUILD and TEST, I left "Constraint End Date" empty. But, because contractually, I need to DELIVER on 10/1, for the DELIVER row, i put "10/1" in the "Constraint End Date" field. Then, I created another column called "Constraint Flag-Test", in which I added the following formula: =IF(NOT(ISBLANK([Constraint End Date]30)), IF([Planned Finish]30 > [Constraint End Date]30, "WARNING", "All-IS-WELL"), "ALL-IS-WELL"). Finally, I did some conditional formatting: If "Constraint Flag-Test" = "WARNING", format the entire row as yellow with red font. Hopefully this helps. It's a pain that you need to create this functionality, but in general, I find so much other value in Smartsheet that I'm willing to live with it. If you have better suggestions, or questions, let me know. (I just started doing this, so also, if you see any flaws in this, please point them out). Note: I sometimes follow this same model for a constrained start date.

  • Kim Galant

    OK, so I'll revisit this one since it's the closest thread that I can find to what I need to do.

    Let's say I have 6 tasks - B-G with different durations (let's say 1-6 days for simplicity's sake) that can all start once task A is finished. So I set the dependency (predecessor) on B-G to A - and Smartsheet figures out that if A ends on Nov 1st, B-G can all start on Nov 2nd. So far so good.

    Now, I know I only have two guys to work on these 6 tasks. But smartsheet is telling me that all of these tasks start on Nov 2nd, and finish 6 days later. While the reality is I need to be splitting them somehow across the two guys I have, to that some will start on the 2nd, others later, and I'll definitely need more than 6 days to do it.

    Now, I also have other things dependent on B-G - so depending on how B-G are ordered in time, the start dates for these dependencies (that aren't constrained by my resources) are impacted. I.e. smartsheet saying that all can start on Nov 2nd leads to an erroneous conclusion that my dependencies start no later than 6 days after.

    So I'd like to figure out a way to reflect the fact that I might do things e.g. this way:

    C(5d) + B(6d)

    G(1d) + F(2d) + E(3d) + D(4d)

    The above gives me a total duration of 11 days for these two streams of work that I'd like to have reflected in my plan. I'm trying to figure out how to best represent this. I see three options:

    1. Manual scheduling (i.e. set the start dates by hand) - not good, since if the dependency A moves, I want both chains to move with it
    2. Creating dependencies from B->C and in the other chain - not good since I could actually be doing them in parallel if I get an extra guy, and if e.g. C all of a sudden needs 8 days, I need to be able to shift things around between the two chains.
    3. Adding lag time to represent the staggered start - again, not a good representation of my resource constraint - no lag time is actually required if I have enough resources.

    What's the 'right' Smartsheet way to represent this in my plan? In MS Project, I'd have used resource leveling to give me what I'm looking for including the recalculation if what's mentioned in #2 above happens.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kim Galant

    It sounds like you've already thought through the different options; my suggestion in this instance would be to use lag time, as you noted. This way you can keep the dependency on a previous task so that the start dates shift as the end date shifts.

    Then if your resources complete what they need to do earlier than expected, they can adjust the dates showing in Smartsheet to the actual finish date for those tasks, which will in turn update the following dependent tasks.

    At Engage this year it was announced that there will be some updates coming to the Gantt chart and Project Settings in 2021, so there may be a better way to do this in the future.



  • Alberto Ferrari

    SS should have this feature by now, incredible that we need to explain why

  • JZummo
    JZummo ✭✭

    This feature would also be critical for me. Our projects revolve around being able to install equipment on specific dates when the manufacturing line is shut down. Those shutdown dates are planned far in advance and must be met. Comparing against a date constraint is a key metric to knowing if the project will be successful or not.

    I got around this by using @Paul Newcome's suggestion, but it's unwieldy and wastes a row and I don't like it:

    Headers: [Health]--[Task Name]--[Start Date]--[End Date]--[Duration]--[State]

    Row 1: This is where I entered my shutdown constraint date in the Start column.

    Row 2: Calculate health in the normal way for a task that's not constrained (I'm using = IF(AND(State@row <> "Complete", TODAY() > ([Finish Date]@row + 20)), "Red", IF(AND(State@row <> "Complete", TODAY() > [Finish Date]@row), "Yellow", "Green")) .)

    Shutdown Row: Calculate health considering the shutdown constraint: =IF([Start Date]@row > $[Start Date]$1, "Red", IF([Start Date]@row > $[Start Date]$1 - 10, "Yellow", "Green"))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Kadin Olson Feel free to browse the “Product Ideas” topic (link at the top of the page) to see if someone has officially submitted this idea as an enhancement request. If they have, then you can add your vote to it to let Smartsheet know you are interested in this feature. If no one has, then you can submit the idea yourself to allow others to vote on it.