Formula Help

I am trying to write a formula that shows "Delayed" if Actual Completion Date is < today or Estimated Completion Date and show "On Time" if Actual Completion Date is => today or Estimated Completion Date.   My formula is getting an unparseable message but I cannot figure out why I am getting an error.

=IF(OR([Actual Completion Date] < TODAY(), [Actual Completion Date] = "", [Estimated Completion Date] < TODAY()), "Delayed", "On Time")

Tags:

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 06/20/23

    Edited from my original post because I misread your post. Add @row to each of your column references.

    =IF(OR([Actual Completion Date]@row < TODAY(), [Actual Completion Date]@row = "", [Estimated Completion Date]@row < TODAY()), "Delayed", "On Time")

  • Manuel Lemus
    Manuel Lemus ✭✭✭✭

    Thanks Carson, the columns are all properly formatted to dates, but it is still showing unparseable.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Just to follow up. If you were leaving this as a single cell formula, you would not need @row for the references, but you would need to specify a row. The below example specifies the first row of each referenced column. The @row tells the formula to reference the current row of each column. Though, technically, @row would work in either case.

    =IF(OR([Actual Completion Date]1 < TODAY(), [Actual Completion Date]1 = "", [Estimated Completion Date]1 < TODAY()), "Delayed", "On Time")

  • Manuel Lemus
    Manuel Lemus ✭✭✭✭


    Ah yes, that helped but now all the data is showing delayed so I may have the incorrect formula, but I re-wrote it to this, and it is working now. :) =IF(ISBLANK([Actual Completion Date]), "", IF([Actual Completion Date] <= [Estimated Completion Date], "On Time", "Delayed"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!