Question about IF AND formula

Eric Heinz

Hello,

 

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

Mitch Shaffer

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?

 

Eric Heinz

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.

 

Eric Heinz

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", ""))

Eric Heinz

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", "")))