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