Welcome!

Question about IF AND formula

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

Find more posts tagged with

Comments

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