Set Status Based on One or More Expiration Dates

Jay F

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.

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

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.

=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

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