Expiry Dates - Compliance Check

jgneely72151

Dear SmartSheet Community, I need your help. I need help in writing a formula that will track compliance for different areas based on expiration dates. If dates are active and will not expire in the next 30 days, health should be green. However, if one or more items in the row will expire in 30 days, yellow and anything greater than the 30 days or perhaps, less than today's date, red. I'm want to make sure I'm notifying individuals within the 30 day window giving them time to update items. Pretty sure I will use automatic alerts as well as thinking about adding a flag column that will automatically flag health that is not green to show non-compliance. Below is how my sheet is arranged. Any assistance is greatly appreciated!

Paul Newcome

Try something like this...

=IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green"))

jgneely72151

Oh my! I have been wrestling quite some time with this using the nested If/Or formula which was super long and the health markers were either red or green, never yellow. Pretty sure I had the logic set up incorrectly or out of order. Anyhow, I really needed to see the yellow so that I can actually start working ahead for updates. Now, I'm able to set automatic send update requests so that this data is updated in a timely manner. Thank you so much!🤗 I do have one more question....suppose I want to also check for missing dates and use a blue or gray health marker. How do I incorporate that into the formula?

Paul Newcome

Assuming your priority is missing dates and then expired dates, it would look something like this...

=IF(COUNTIFS([Exp Date1]@row:[Exp date3]@row, "") > 0, "Blue", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green")))

Paul Newcome

Ok. I think I understand what you are wanting (but please feel free to correct).

You want to use the first formula of

=IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green"))

but you want to generate "Blue" when you get an error for rows that have all 3 columns as blank.

=IFERROR(IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green")), "Blue")

Paul Newcome

Try something like this...

=IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green"))

jgneely72151

Oh my! I have been wrestling quite some time with this using the nested If/Or formula which was super long and the health markers were either red or green, never yellow. Pretty sure I had the logic set up incorrectly or out of order. Anyhow, I really needed to see the yellow so that I can actually start working ahead for updates. Now, I'm able to set automatic send update requests so that this data is updated in a timely manner. Thank you so much!🤗 I do have one more question....suppose I want to also check for missing dates and use a blue or gray health marker. How do I incorporate that into the formula?

Paul Newcome

Assuming your priority is missing dates and then expired dates, it would look something like this...

=IF(COUNTIFS([Exp Date1]@row:[Exp date3]@row, "") > 0, "Blue", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green")))

jgneely72151

Thanks! After executing the formula, it worked. The first few rows are the IF(MIN) formula and the last few rows is the If(COUNTIFS) formula. While it is definitely doing what I want it to do, I'm receiving the #INVALID OPERATION because I have rows where there missing data is in each of the cells. I prefer to use the latter function but what should I do about these missing cells? If blue doesn't work then perhaps I should just have it return "Missing Date"?

jgneely72151

Opps forgot to attach the example.

jgneely72151

By the way, I'm ok with where there are 1 or 2 cells missing data because it is going to be either red or yellow but not sure what to do about the entire row of missing cells. Sorry for the multiple posts.

Paul Newcome

I'm not sure why it isn't working. It should actually be flagging any row with at least one blank as "Blue" the way it is written, but it's like it isn't registering any blanks at all.

How is the data in the date columns being entered? Is it a formula or manual entry?

jgneely72151

My apologies Paul....losing my scrupples.

Ignore what I said earlier about the examples of the formulas. I decided to use the 1st formula you gave me since it is doing exactly what I want it to do. I wanted to see what would happen when missing data is in all of the fields and I got #INVALID OPERATION. No problem! I do see that it is not meeting the criteria of that formula's logic. However, I like what the formula is returning to me for all other rows, even for the rows where there are 1 or 2 cells of missing data. At least if it is red or yellow, I can let the individual know that there is missing data to be provided along with those items to renew. I only want to have the blue register if there is missing data in all of the cells. I can then do something a little different with those individuals. Hope that makes sense.

Paul Newcome

Ok. I think I understand what you are wanting (but please feel free to correct).

You want to use the first formula of

=IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green"))

but you want to generate "Blue" when you get an error for rows that have all 3 columns as blank.

=IFERROR(IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(), "Red", IF(MIN([Exp Date1]@row:[Exp date3]@row) <= TODAY(30), "Yellow", "Green")), "Blue")

jgneely72151

Yes!!!! That's it! Perfect! Thank you so much!

Paul Newcome

Great! Happy to help! 👍️