# Invalid Operation formula question

edited 12/09/19

The following formula is pulling an #INVALID OPERATION error when a date is entered into the Date Received column - anyone have an idea of what the error is? The column date is set to Date. Thanks in advance!

=IF([Date Received]11 = 0, IF(\$[Q3 Estimated Completion Date]\$8 > [Q3 Estimated Completion Date]11, "Late & Outstanding", IF(\$[Q3 Estimated Completion Date]\$8 = [Q3 Estimated Completion Date]11, "Due Today Not Rec", "Not Yet Due")), IF([Date Received]11 < [Q3 Estimated Completion Date]11, "Early", IF([Q3 Estimated Completion Date]11 > ([Date Received]11 - 1), "On Time", "Rec Late")))

• ✭✭✭✭✭✭

Hi Jess,

Is the [Q3 Estimated Completion Date] also a date formatted column?

The inclusion of IF([Date Received]11 < [Q3 Estimated Completion Date]11 and IF([Q3 Estimated Completion Date]11 > ([Date Received]11 - 1) will produce an error if you are comparing a date format to a text/number format.

The easiest way to debug is to copy each IF statement out separately and then see which one is producing the error.

• Hi Chris,

Yes - the [Q3 Estimated Completion Date] column is also formatted as a date.

I think it may be the last string - IF([Date Received]11 < [Q3 Estimated Completion Date]11, "Early", IF([Q3 Estimated Completion Date]11 > ([Date Received]11 - 1), "On Time", "Rec Late"))) but not 100% sure and unsure how to fix. I have dates populated in the [Q3 Estimated Completion Date] and the formula will produce "Late & Outstanding", "Due Today Not Rec", or "Not Yet Due" when nothing is in the [Date Received] cell. That's when I get the Invalid Operation error.

• ✭✭✭✭✭✭

Hi Jess,

I have created a mockup with the 2 date columns specified and I am not receiving any errors. That being said, the formula may not be calculating dates/statuses in the way you imagine.

Your formula is evaluating against the following criteria:

1. IF [Date Received]11 is blank AND [Q3 Estimated Completion Date]\$8 is greater than [Q3 Estimated Completion Date]11 then the status is "Late & Outstanding"
2. IF [Q3 Estimated Completion Date]\$8 is equal to [Q3 Estimated Completion Date]11 then the status is "Due Today Not Rec"
3. IF [Date Received]11 is blank AND condition 2 is not true then the status is "Not Yet Due"
4. IF [Date Received]11 is less than [Q3 Estimated Completion Date]11 then the status is "Early"
5. IF [Q3 Estimated Completion Date]11 is greater than [Date Received]11 - 1 day then the status is "On Time"
6. If all other conditions are false then the status is "Rec Late"

Is this what you were after?

• Hi Chris,

Thanks again for your response - I think there may have been an error in my criteria. They should be:

1. IF [Date Received]11 is blank AND [Q3 Estimated Completion Date]\$8 is greater (after) than [Q3 Estimated Completion Date]11 then the status is "Late & Outstanding"
2. IF [Q3 Estimated Completion Date]\$8 is equal to [Q3 Estimated Completion Date]11 AND [Date Received]11 then the status is "Due Today Not Rec"
3. IF [Date Received]11 is blank AND condition 2 is not true then the status is "Not Yet Due"
4. IF [Date Received]11 is less (earlier) than [Q3 Estimated Completion Date]11 then the status is "Early"
5. IF [Q3 Estimated Completion Date]11 is equal to [Date Received]11 then the status is "On Time"
6. If [Date Received]11 is greater (after) [Q3 Estimated Completion Date]11 then the status is "Rec Late"
• Hi Jess,

I don't think dates can be compared to numbers (including zero), even though it does allow you to compare a BLANK cell to zero. So you might change the first condition in the formula:

to:

Hopefully that will work for blank cells and dated cells.

• ✭✭✭✭✭✭

Hi Jess,

Diane is correct abut using ISBLANK instead of =0. It's best practice to use ISLANK regardless of whether the formula actually works.

Based on your criteria (which I'll have to trust is correct as I don't know the structure of your sheet), but this will work:

=IF(AND(ISBLANK([Date Received]11), [Q3 Estimated Completion Date]\$8 > [Q3 Estimated Completion Date]11), "Late & Outstanding", IF(AND([Q3 Estimated Completion Date]\$8 = [Q3 Estimated Completion Date]11, [Q3 Estimated Completion Date]\$8 = [Date Received]11), "Due Today Not Rec", IF(AND(ISBLANK([Date Received]11), [Q3 Estimated Completion Date]\$8 = [Q3 Estimated Completion Date]11, [Q3 Estimated Completion Date]\$8 = [Date Received]11), "Not Yet Due", IF([Date Received]11 < [Q3 Estimated Completion Date]11, "Early", IF([Q3 Estimated Completion Date]11 = [Date Received]11, "On Time", IF([Date Received]11 > [Q3 Estimated Completion Date]11, "Rec Late", ""))))))

• That worked! Thanks so much Diane and Chris!

• ✭✭✭✭✭✭

Hi Jess,

Happy to hear that. Best of luck with your sheet .