#invalid operation (new w/update)

In addition to the new features of Smartsheet brought about with the latest update, I'm also now getting #invalid operation error with this formula:

=IF(AND([Start Date]285 - TODAY() = 0, [End Date]285 - TODAY() = 0), "Gray", IF(AND([Start Date]285 - TODAY() <= 45, [Start Date]285 - TODAY() <> 0), "Yellow", IF(AND([Start Date]285 - TODAY() <= 90, [Start Date]285 - TODAY() <> 0), "Green", IF(AND([End Date]285 - TODAY() <= 45, [End Date]285 - TODAY() <> 0), "Yellow", IF(AND([End Date]285 - TODAY() <= 90, [End Date]285 - TODAY() <> 0), "Green", "Gray")))))

Ruh-roh, Raggy.

There's probably a better formula anyhow, right J.Craig?

Kind Regards,

Preston

Find more posts tagged with

Comments

Preston

Does it have something to do with the "more precise" error evaluations? I think the kicker is:

[Start Date]285 - TODAY() >= 0

If [Start Date]285 is blank, it returns an error. What changed, and I can fix it?

Nick DeLaO

HELP!! Same problem! I need this fixed asap! I run all of our allocations based off the below functions and they worked FINE prior to the update!

=SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 1, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 2, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 3, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 4, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 5, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 6, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 7, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 8, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 9, [Assigned To]:[Assigned To], "AJ Gregg") + SUMIFS([Available Billable Hrs Remaining]:[Available Billable Hrs Remaining], Priority:Priority, 10, [Assigned To]:[Assigned To], "AJ Gregg")

Preston

Well, after spinning my wheels trying to figure out what changed, I decided to change my approach. This seems to work a little better for me:

=IF(ISBLANK([Start Date]217), "Update", IF([Start Date]217 - TODAY() <= 45, "Yellow", IF([Start Date]217 - TODAY() <= 90, "Green", IF([End Date]217 - TODAY() <= 45, "Yellow", IF([End Date]217 - TODAY() <= 90, "Green", "Gray")))))

I hope everyone else is able to maintain functionality.

Kind Regards,

Preston

J. Craig Williams

I was getting #Invalid Operation on empty date cells.

That's probably the cause.

I wrapped them with IFERROR( old formula, "No Date") and it fixed it.

Your ISBLANK is doing the same thing.

Your new formula is very nice.

I was going to say something about the order but then realized that if you start date is 46 days out, your end date has to bev 46 or more so your order is fine.

You might save processing time by switching to two OR()'s but it is I don't have the proper tools to do an adequate timing check so it just "feels faster".

Craig

Preston

Hey, thanks! I hadn't yet tried the new formula additions because I was trying to fight what they broke! Thanks again for the input, Craig!