LIttle Help With Formula

steve50951

I have a helper column (checkbox) that fills the checkbox in if the "D/I" date is past 14 days...30...45, AND IF "install date" is blank.

Here's the formula i have that seems to be working so far

=IF([D/I]@row < TODAY(-14), IF(ISBLANK([Install Date]@row), 1, 0))

couple of ?'s...

1. If i fill in the D/I with a date, and then change it to blank, the checkbox stays filled in. I need the checkbox to go un-checked if the D/I date is changed to blank.

2. Will the formula hold up over time? Do you see any issues with it?

Thanks,

 

Steve

Andrée Starå

Hi Steve,

Your formula won't work because it would always check the box.

Try something like this.

=IF(ISBLANK([D/I]@row); ""; IF(AND([D/I]@row < TODAY(-14); ISBLANK([Install Date]@row)); 1))

The same version but with the below changes for your and others convenience.

=IF(ISBLANK([D/I]@row), "", IF(AND([D/I]@row < TODAY(-14), ISBLANK([Install Date]@row)), 1))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

steve50951

Works perfect!

Thanks!

Steve

Andrée Starå

Excellent!

Happy to help!

steve50951

so i found 1 exception i need to add to the formula. I included a screenshot.

Here's the formula im using...

=IF(ISDATE([D/I]@row), IF([D/I]@row < TODAY(-14), IF(ISBLANK([Install Date]@row), 1, 0))).

I need to add a condition...if the "unit #" column contains "return to tote", then i need it to return a 0 value (leave the box unchecked).

not sure how to add that condition to the formula.

thanks

 

Andrée Starå

Happy to help!

Can you maybe share the sheet or a copy of it? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)