Only perform calculation if cell is not blank

Optimizer

I currently have a sheet and one of the functions I want is to check a box if the Date Won/Lost is older than 365 days.

My current formula is: =IF([Date Final (Won/Lost)]@row < TODAY(-365), 1, 0)

However, when the Date Won/Lost cell is blank (because the contract hasn't closed or been abandoned yet), the formula returns a "check" as if the blank cell is older than 365 days.

I've read through a number of threads and tried a bunch of ISBLANK, NOT ISBLANK, multiple IFs, IF/AND, etc. and either get no result, the wrong result, or UNPARSEABLE.

Help!

Andrée Starå

Hi,

Try something like this.

=IF([Date Final (Won/Lost)]@row = ""; 0; IF([Date Final (Won/Lost)]@row < TODAY(365); 1))

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

=IF([Date Final (Won/Lost)]@row = "", 0, IF([Date Final (Won/Lost)]@row < TODAY(365), 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?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Optimizer

YES!!!  Thank you!!  (Me jumping up and down for joy)

Andrée Starå

Excellent! 

Happy to help!

Best,

Andrée