#### 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.

# #invalid operation (new w/update)

Options
✭✭✭✭✭
edited 12/09/19

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

• ✭✭✭✭✭
Options

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?

• edited 08/07/16
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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!

Kind Regards,

Preston

This discussion has been closed.