# Validation Formula

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Employee
Options

Awesome! Let us know how it goes.

October 8 - 10, Seattle, WA | Register now

• Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

This community is:

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | 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!