Set Status Based on One or More Expiration Dates

Hello Community,

I have a sheet where the primary column is a user name. Then, a status column with RYG. Then, alternating columns of checkboxes for a unique certification followed by a column with the expiration date of the certification.

I would like to set the status based on the following conditions:

Red = any certification is expired

Yellow = at least one certification is scheduled to expire within 30 days

Green = all certifications that are checked expire more then 30 days from now

My formula for the status column is...

=IF((ISDATE([Administrator]@row:[Application Architect Expiration]@row),MIN([Administrator]@row:[Total Certifications]@row) <= TODAY(-30), "Red", IF(MIN([Administrator]@row:[ Application Architect Expiration]@row) <= TODAY(30), "Yellow", "Green")))

Following is a screenshot of my sheet.

Thank you for your assistance.

Find more posts tagged with

Accepted answers

Mark Cronk

@Jay F ,

My mistake. Try this. The COLLECT function is (Range, criterion range1, criterion1,...). I left out the criterion range 1.

=IF(MIN(COLLECT([Administrator]@row:[Total Certifications]@row, [Administrator]@row:[Total Certifications]@row, ISDATE(@cell)=1)) <= TODAY(-30), "Red", IF(MIN(COLLECT([Administrator]@row:[Total Certifications]@row, [Administrator]@row:[Total Certifications]@row, ISDATE(@cell)=1)) <= TODAY(30), "Yellow", "Green"))

Work this time?

Mark

All comments

Mark Cronk

Hi @Jay F ,

Try

=IF(MIN(COLLECT([Administrator]@row:[Total Certifications]@row, ISDATE(@cell)=1) <= TODAY(-30), "Red", IF(MIN(COLLECT([Administrator]@row:[Total Certifications]@row, ISDATE(@cell)=1) <= TODAY(30), "Yellow", "Green"))

Because your rows contains checkboxes and dates you need to use COLLECT to look at just the dates.

Does it work for you?

Mark

Jay F

Hi @Mark C.

Thank you for the reply, I tried your suggestion and get "INCORRECT ARGUMENT SET" as an error message. I'm still learning here, but it is possible the date range of 30 is the issue?

Mark Cronk

@Jay F ,

My mistake. Try this. The COLLECT function is (Range, criterion range1, criterion1,...). I left out the criterion range 1.

Work this time?

Mark

Jay F

Thank you @Mark C.

For reference, I tweaked the date values in the TODAY function to get the colors correct. Here is my final formula

=IF(MIN(COLLECT(Administrator@row:[Total Certifications]@row, Administrator@row:[Total Certifications]@row, ISDATE(@cell) = 1)) <= TODAY(), "Red", IF(MIN(COLLECT(Administrator@row:[Total Certifications]@row, Administrator@row:[Total Certifications]@row, ISDATE(@cell) = 1)) <= TODAY(30), "Yellow", "Green"))

Mark Cronk

Beautiful. Glad you figured it out. Appreciate you accepting my answer.

Mark

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.