Formula not working


I have a late task checkbox that isn't working. The formula is to compare the baseline start and finish duration to the actual start and finish duration, if the actual dates are longer, the checkbox should automatically check.

=IF(AND(NOT(ISBLANK(Duration@row)), [Duration (Actual)]@row > Duration@row, ([Finish (Actual - Jira)]@row < [Finish (Baseline)]@row)), 1, 0)

Is there something wrong with my formula?

Best Answer


  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @M Morgan Technically the formula works for me. Is that the trouble you are having, or are you not getting expected results?

    Keep in mind that using AND means all three conditions must be met. The formula also mixes a comparison between numbers and a comparison between dates; it works but it is a bit confusing.



  • The problem is, when the baseline date is missed, the box doesn't check. Regarding the dates and numbers, we had a problem with our original formula - when a task started early and finished on-time, the box would check so we updated to this formula but now nothing is happening.

    Any guidance will be much appreciated.


  • M Morgan
    M Morgan ✭✭
    Answer ✓

    Thank you, I changed the formula to OR and it works!

  • I thought I would share an update, changing the formula to OR only means that it now marks all line items late when a baseline date has been added, even if the actual is blank (still in progress).

    What I'm looking for is the following:

    If the actual completion date is later than the baseline completion date, OR if the actual completion date is blank (because it's still in progress) but the calendar date (that matches the baseline completion date) has passed, the checkbox needs to be checked. Is there a formal anyone can help me with for that?

    Again, I appreciate any guidance, thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!