IF, OR and AND? Formula issue

Hi, I'm trying to calculate if we're meeting the schedule start date.

The issue I have is if the Schedule Start Date is greater than today's date then it still thinks its completed on time. I think I'm missing AND statement with the formula?

=IF(OR([Actual Start Date]@row = "", [Schedule Start Date]@row < [Actual Start Date]@row), IF([Schedule Start Date]@row < Today@row, "Overdue", "Scheduled"), "On Time")

To summarize what I want the formula to calculate:

  1. If the actual start date is blank, check to see if the schedule start date is less than today's date = "Overdue", if not its "scheduled" for completion.
  2. If the actual start date has a date, compare it against the schedule start date, if the date is more than the schedule start date its "overdue", if its before the schedule start date or the same date its "On Time"

Thanks :)


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @Jack Parry,

    Something like this?

    =IF(OR(AND([Actual Start Date]@row = "", [Schedule Start Date]@row < TODAY()), [Actual Start Date]@row > [Schedule Start Date]@row), "Overdue", IF(AND([Actual Start Date]@row = "", [Schedule Start Date]@row > TODAY()), "Scheduled", "On Time"))

    Example output:

    Hope this helps, but I've misunderstood something or you've any problems/questions then just post! πŸ™‚


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!