Invalid data type when using formula

Options

I am trying to create health status column in my sheet and I want it to generate for me a colored ball in the column and I am using this formula: =IF([% Complete]@row, "Blue", IF([Due Date]@row, <TODAY(), "Red", IF([Due Date]@row <= TODAY(5), "Yellow", "Green")))

I am getting the INVALID DATA TYPE message in some rows, INCORRECT ARGUMENT SET in some rows and in some rows it's working fine. Please advise.



Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 08/11/23
    Options

    Hello @Latreva Washington,

    What is the logic you would like to have for the health bar? I am trying to determine what blues purpose is but there is no logical expression there to set blue to true, this may be one of your issues.

    If you'd like blue to be logic for no data in %complete column, you can try this formula

    =IF([% Complete]@row = "", "Blue", IF([% Complete]@row = "100", "Green", IF([Due Date]@row <= TODAY(5), "Yellow", IF([Due Date]@row > TODAY(0), "Red"))))


    I look forward to hearing back from you to help resolve this issue!

    Sincerely,

    Jacob Stey

  • Latreva Washington
    Options

    Logic - please let me know if this doesn't make sense. I am still learning how to understand what formulas are 'saying'

    Complete = Blue

    In progress - on schedule = Green

    In Progress - <= TODAY(5) = Yellow

    Past Due - Due date greater than today = RED

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    Options

    Thank you @Latreva Washington,

    You can try this formula

    =IF([% Complete]@row = "", "Green", IF([% Complete]@row = "100", "Blue", IF([Due Date]@row <= TODAY(5), "Yellow", IF([Due Date]@row > TODAY(0), "Red"))))

    Hope this helps!

    Sincerely,

    Jacob Stey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!