Invalid Operation formula question

Jess McCabe

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

Chris McKay

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.

Jess McCabe

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.

Chris McKay

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?

Jess McCabe

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"

Diane Zevenbergen

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:

[Date Received]11 = 0

to:

ISBLANK([Date Received]11)

Hopefully that will work for blank cells and dated cells.

Chris McKay

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

Jess McCabe

That worked! Thanks so much Diane and Chris!

Chris McKay

Hi Jess,

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