Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Question about IF AND formula

Eric Heinz
edited 12/09/19 in Archived 2017 Posts



I'm trying to use this formula on my smartsheet to create a column indicating "Late" if the phase is not closed and behind schedule, or "Missed" if the phase was approved, but after the target due date.  It is coming back UNPARSEABLE.  I apprecate any insight into what I'm doing wrong!  Should I be using ISBLANK instead of if the submitted date = 0? 


=IF(AND([Phase Target Date]2 < TODAY(), [Phase Submitted Date]2 = 0), "Late", IF([Approved Date]2 > [Approved Target Date]2, "Missed", "")))


Thank you!



  • Eric,


    Give this a try:


    =IF(ISBLANK([Phase Submitted Date]4), "Late", IF([Phase Target Date]4 < TODAY(), "Late", IF([Approved Date]4 > [Approved Target Date]4, "Missed")))


    It might need some tweaking depending on what is most important to display in that column. What do you want it to show if a row was both Late and Missed?


  • Hi Mitch,


    Thank you for your feedback.  It's important to capture the "AND" requirement of both ISBLANK([Phase Submitted Date]4 AND [Phase Target Date]4 < TODAY() to result in "Late" if true. 


    In my case I will never run into a situation where it is both late and missed because the if the phase submitted date is null, there couldn't be an approved date.


  • After working on this further, I found the solution:

    =IF(ISBLANK([Phase Submitted Date]2), IF([Phase Target Date]2 < TODAY(), "Late", ""), IF([Approved Date]2 > [Approved Target Date]2, "Missed", ""))

  • If Phase Target Date is null, which may be in the case a phase hasn't yet been planned, the previous formula would have returned with "Late".


    I fixed that by adding another If function:

    =IF(ISBLANK([CAPA Phase Target Date]2), "", IF(ISBLANK([CAPA Phase Submitted Date]2), IF([CAPA Phase Target Date]2 < TODAY(), "Late", ""), IF([CAPA Approved Date]2 > [CAPA Approved Target Date]2, "Missed", "")))

This discussion has been closed.