Have you ever had a situation where a tested, tried and true formula was producing an “incorrect” result?

Recently one of my client schedulers wanted to know, “How can a formula produce correct results for 500 other tasks, but produce an obviously incorrect result for only one task?”

In her schedule she was using this formula in the Flag 7 field:

IIf([Scheduled Start]<=[Status Date]+1 And [% Complete]<100,Yes,No)

It’s obvious that the formula is intended to look at the Scheduled Start date and compare it to the Status Date as set by the scheduler. If the task start date is prior to the status date AND the task isn’t complete, the formula is supposed to render a “yes”; if the condition is false, it renders a “no.”

By filtering for “yes” the scheduler quickly determines which tasks for a given week require an update. But after running successfully for six months, the formula was rendering an “obviously incorrect” result in row 902, as shown below.

As the novice pointed out “… and anybody can see the Status Date is correctly set for next Friday, and that task 902 isn’t even scheduled to start for a couple of weeks, so why is the formula displaying ‘yes’ when it should be coming up with ‘no’?”

I tried using a Status Date further out in the future, and as I expected, other tasks began showing up with “incorrect” results for Flag 7. Now I was sure that nothing was wrong with the formula or the task, but what did all of the “incorrect” Flag 7 tasks have in common?

Instinctively, I inserted the Task Mode field and noticed all the tasks with “incorrect” information were set to “Manually Scheduled.” When the Task Mode was set to “Auto Scheduled,” the flag changed from a “yes” to a “no,” indicating that the task was in the future and didn’t require an update by 12-02 2016.

“Problem” solved!

Image Source