Invalid Data Error
Good morning everyone.
I have a formula to count the number of days between two dates column. In one column, similar formula is working but in the other column (with different reference data) it does not. Can someone take a look and advise what I am missing. I searched in the community to see if there's a similar topic that I can review. There were few and I tried but still giving me error.
Here is the formula that is working as I intended (No of Days after PPAP Due Date)
=IF(ISBLANK([Final PPAP Approval Status]@row), "", IF([PPAP Inspection Turn Around]@row, 0, NETWORKDAYS([Inspection Due Date]@row, [Date Inspection Completed]@row)))
Here is the second formula that gives me invalid data type (No of Days after 3F+P Completion).
=IF(ISBLANK([3F+P Result]@row), "", IF([Parts Disposition]@row, "Subject for 3F+P", NETWORKDAYS([3F+P Completion Date]@row, [Inspection Due Date]@row)))
Here is the screenshot of the database.
Thank you in advance for the help.
Cheers!!
Best Answer

Ok, you have some errors in both formulas that may help clear some things.
No of Days after PPAP Due Date Formula, you were getting the false thinking it was true. Just added the equal.
=IF(ISBLANK([Final PPAP Approval Status]@row), "", IF([PPAP Inspection Turn Around]@row = 0, NETWORKDAYS([Inspection Due Date]@row, [Date Inspection Completed]@row)))
No of Days after 3F+P Completion formula, same thing, you had a comma where the equal should be
=IF(ISBLANK([3F+P Result]@row), "", IF([Parts Disposition]@row = "Subject for 3F+P", NETWORKDAYS([3F+P Completion Date]@row, [Inspection Due Date]@row)))
Hope that works!
Answers

@RaffyM That is kind of a weird, as that column should be blank based off of your formula. Also, have you restrict the date columns to date only?

Hi Eric,
Thanks for the quick response. I recheck and it is a date column.

@RaffyM are those the exact formulas you have in your columns?

@Eric Law Yes, that's the same formula in my columns. In fact, I just copy and paste it here.
If I remove one criteria and write the formula as this: =IF(ISBLANK([3F+P Result]@row), "", NETWORKDAYS([Inspection Due Date]@row, [3F+P Completion Date]@row)), it worked
But I need that second criteria because it some cases the "Parts Disposition" data is not applicable.
=IF(ISBLANK([3F+P Result]@row), "", IF([Parts Disposition]@row, "Subject for 3F+P", NETWORKDAYS([3F+P Completion Date]@row, [Inspection Due Date]@row)))

Ok, you have some errors in both formulas that may help clear some things.
No of Days after PPAP Due Date Formula, you were getting the false thinking it was true. Just added the equal.
=IF(ISBLANK([Final PPAP Approval Status]@row), "", IF([PPAP Inspection Turn Around]@row = 0, NETWORKDAYS([Inspection Due Date]@row, [Date Inspection Completed]@row)))
No of Days after 3F+P Completion formula, same thing, you had a comma where the equal should be
=IF(ISBLANK([3F+P Result]@row), "", IF([Parts Disposition]@row = "Subject for 3F+P", NETWORKDAYS([3F+P Completion Date]@row, [Inspection Due Date]@row)))
Hope that works!

@Eric Law yes, it works!
Thank you for checking and correcting the error. Appreciate your help! 😉
Help Article Resources
Categories
Check out the Formula Handbook template!