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

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @RaffyM

    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

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    edited 06/06/23

    @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?


  • RaffyM
    RaffyM ✭✭✭✭

    Hi Eric,

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


  • Eric Law
    Eric Law ✭✭✭✭✭✭

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

  • RaffyM
    RaffyM ✭✭✭✭

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

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @RaffyM

    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!

  • RaffyM
    RaffyM ✭✭✭✭

    @Eric Law yes, it works!

    Thank you for checking and correcting the error. Appreciate your help! 😉

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!