Need to look at a date and check box based on 3 criteria

Peggy Parchert

I am trying to make a formula that looks at a date field and checks box if

1) Task date is over 3 years ago;

2) Retired isn't checked;

3) Type is a specific kind (Type S)

I can get the first part to work but I always run into problems with IF statements that have multiple criteria.

=IF(ISDATE([Task Last Completed]@row), IF([Task Last Completed]@row <= TODAY(-1096), 1)) is what I currently have that works but it doesn't look at the two additional criteria I need it to.

I've attached a screenshot of my sheet. The above formula is in the Over 3 years column.

Any help would be greatly appreciated.

Peggy

Screen Shot 2020-02-03 at 12.57.14 PM (2).png

Jayesh Nathani

Hi Peggy,

Hope this helps!

=IF(AND(YEAR(TODAY()) - YEAR([Task Last Completed]@row) >= 3, Retired@row = 0, Type@row = "Type S"), 1, 0)

Peggy Parchert

@Jayesh Nathani - thank you very much! That worked! I'd never used the YEAR function but now I'm reviewing all my sheets to see if I can modify it - thank you!

Jayesh Nathani

Happy to help :)

Have a great day.