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
Preston
Preston ✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

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

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

 

Kind Regards,

Preston

Comments

  • Preston
    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? :)

  • Nick DeLaO
    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")

  • Preston
    Preston ✭✭✭✭✭
    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

  • Preston
    Preston ✭✭✭✭✭
    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.