# ISBLANK, IF, AND combination

Options
edited 01/06/22

Hello,

I am trying to get the below formula to work however am not having much luck.

=IF(ISBLANK([Customer Owner]9, 0, IF(AND([Start Date]9 < TODAY(), [End Date]9 > TODAY()), 1)))

I would like a check box ticked if the [Customer Owner] is NOT blank and today is between the [Start Date] and [End Date].

TIA

Options

Got it!!

=IF((ISBLANK([Customer Owner]11)), 0, IF(AND([Start Date]11 < TODAY(), [End Date]11 > TODAY()), 1))

• ✭✭✭✭✭
Options

So using plain language: IF Customer Owner is NOT BLANK AND Start Date is Less Then Today AND End Date is Greater Then Today then Check

So... IF(AND(NOT(BLANK(Expression1)), Expression 2, Expression 3), True, False)

Try This

=IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row < TODAY(), [End Date]@row > TODAY()), 1, 0)

In the expression, I have written if it was to start on today's date it would not show checked..

=IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), 1, 0)

Would include today's date as within the start and end date rage

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

Options

Got it!!

=IF((ISBLANK([Customer Owner]11)), 0, IF(AND([Start Date]11 < TODAY(), [End Date]11 > TODAY()), 1))

• ✭✭✭✭✭✭
Options

Hi @JamieN

I hope you're well and safe!

Excellent!

Have a fantastic week & Happy New Year!

Best,

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

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.

• ✭✭✭✭✭
Options

So using plain language: IF Customer Owner is NOT BLANK AND Start Date is Less Then Today AND End Date is Greater Then Today then Check

So... IF(AND(NOT(BLANK(Expression1)), Expression 2, Expression 3), True, False)

Try This

=IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row < TODAY(), [End Date]@row > TODAY()), 1, 0)

In the expression, I have written if it was to start on today's date it would not show checked..

=IF(AND(NOT(ISBLANK([Customer Owner]@row)), [Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), 1, 0)

Would include today's date as within the start and end date rage

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭✭
Options

I have a similar formula need;

IF 'Target Complete Date' (this is a date) is blank AND 'Target Complete Date' is older than ('Survey Date'-60 days) then 'check' 'HELPER-Date in Target Complete Date?'

• ✭✭✭✭✭✭
Options

@PeggyLang Can you clarify? You are saying if it is blank and if it is older than the survey date, but if it is blank then it cannot be in the past or future. Do you mean OR?

=IF(OR([Target Complete Date]@row = "", [Target Complete Date]@row<= [Survey Date]@row - 60), 1)

• ✭✭✭✭✭
Options

@Paul Newcome If it is blank AND todays' date is greater than [Survey Date]@row - 60 then I need the HELPER column checked.

• ✭✭✭✭✭✭
Options

@PeggyLang In that case:

=IF(AND([Target Complete Date]@row = "", TODAY()>= [Survey Date]@row - 60), 1)

• ✭✭✭✭✭
Options

@Paul Newcome I REALLY appreciate YOU!!! I get very discombobulated when combining formulas; haven't really figured out how to think about the individual parts and where they go. Your formula works perfectly. THANK YOU!!

• ✭✭✭✭✭✭
Options

@PeggyLang Happy to help. 👍️

• ✭✭✭✭✭
edited 07/03/23
Options
• Options

@Paul Newcome I have a slightly different formula needed.

If [procedure date] is blank to combine with the following formula (which is working fine).

=IF([Days from clinic date to today's date]@row < 30, "1-30 days", IF([Days from clinic date to today's date]@row < 40, "31-40 days", IF([Days from clinic date to today's date]@row < 50, "41-50 days", IF([Days from clinic date to today's date]@row < 60, "Over 60 days", IF([Days from clinic date to today's date]@row > 60, "More than 60 days")))))

Help appreciated!

• ✭✭✭✭✭✭
Options

@mary twomley Where would it fit in with the rest of the logic. If that field is blank then ignore the rest of the formula, or if all of the other arguments are false and the field is blank then output a blank?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!