Validation Formula

Going crazy.

My current formula

=IF(AND([Time Missed / Used (In Hours)]1 < 19.99),CONTAINS("Not", [Excused/Not Excused]1:[Excused/Not Excused]1, ".25", "1"))) = #UNPARSEABLE

The goal here is to return .25 or 1 into a cell "Totals" if cell "Time Missed / Used (In Hours)]" is less than 19.99 and cell "[Excused/Not Excused]". Default value of cell "Totals" = 0.

IF cell "[Excused/Not Excused]" contains "excused" THAN cell "Totals" =0

IF cell "[Excused/Not Excused]" contains "Not" and cell "[Time Missed / Used (In Hours)" GREATER than 19.99, Cell "Totals" = 1

IF cell "[Excused/Not Excused]" contains "Not" and cell "[Time Missed / Used (In Hours)" is GREATER 1 but less THAN 19.99, Cell "Totals" = .25

Best Answers


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Based on what I read in the post, I feel like maybe the CONTAINS functions should be using the @row reference instead of the column reference.

    =IF([Excused/Not Excused]@row = "Excused", 0, IF(AND([Time Missed / Used (In Hours)]@row > 19.99, CONTAINS("Not", [Excused/Not Excused]@row)), 1, IF(AND([Time Missed / Used (In Hours)]@row < 19.99, [Time Missed / Used (In Hours)]@row >= 1, CONTAINS("Not", [Excused/Not Excused]@row)), 0.25, 0)))

    I also agree that screenshots would be helpful though. If your [Excused/Not Excused] column contains either the option of "Excused" or "Not Excused", the above formula will never display anything other than 0 because "Not Excused" does contain the text of "Excused" which in turn causes the first IF to be true.

  • Thank you all! This helps tons I will let you know if this works!! This should close out my 2019 - 2020 Q1 goal!!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

  • Genevieve P.
    Genevieve P. Employee Admin

    Awesome! Let us know how it goes.

  • This worked perfectly. Thank you for the help. Love this community!

  • Genevieve P.
    Genevieve P. Employee Admin

    So glad to hear that! Thanks for the follow-up

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Great!! This community is definitely a good one full of a ton of information.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!