ISBLANK, IF, AND combination

JamieN

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

JamieN

Got it!!

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

Brent Wilson

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)

Be careful about your <... you may want them as <=

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

JamieN

Got it!!

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

Andrée Starå

Hi @JamieN

I hope you're well and safe!

Excellent!

Glad you got it working!

Have a fantastic week & Happy New Year!

Best,

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

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Brent Wilson

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)

Be careful about your <... you may want them as <=

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

PeggyLang

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

Paul Newcome

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

PeggyLang

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

Paul Newcome

@PeggyLang In that case:

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

PeggyLang

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

Paul Newcome

@PeggyLang Happy to help. 👍️

PeggyLang

Mary McCabe

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

Paul Newcome

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