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

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

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.