nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Formula to Indicate Current Month in 90-Day Grace Period for 26 Certifications

Lori Flanigan

I have a sheet that list people on a team in rows with columns containing 26 certification types. Each certification type has a certification date that is also the start date of a 90-day grace period. I have included an end date field for the grace period and a checkbox field to indicate if the person in the grace period for any of the certifications.

The end user wants a report to include if any person has a certification in a grace period for the current month.

I added checkbox helper fields for January-December and wrote this formula that works for one certification (Email- Admin II):

=IFERROR(IF(OR(AND(ISDATE([Email- Admin II]@row), YEAR([Email- Admin II]@row) = YEAR(TODAY()), MONTH([Email- Admin II]@row) = 3), AND(ISDATE([Email Grace End Date]@row), YEAR([Email Grace End Date]@row) = YEAR(TODAY()), OR(MONTH([Email Grace End Date]@row) = 3, MONTH([Email Grace End Date]@row) = 4, MONTH([Email Grace End Date]@row) = 5))), 1, 0), "")

Is there another way to approach this so that I don't have to have January-December fields for each of the 26 certifications?

Thanks for any help!

Lori Flanigan

Find more posts tagged with

Formulas

Accepted answers

DKazatsky2

Hi @Lori Flanigan,

I have one idea that I think would work but requires the use of a "helper" column. This new column would be a "date" type and would be used to track the last day of the month for the grace period end. In my example, I called the column "Last Day of Grace Period End Month" - this column can be hidden. This is the formula used.

=IFERROR(DATE(YEAR([Cert 1 Grace End Date]@row), MONTH([Cert 1 Grace End Date]@row) + 1, 1), DATE(YEAR([Cert 1 Grace End Date]@row) + 1, 1, 1)) - 1

NOTE: You will need one of these for each cert type

Next, use the following formula in the checkbox columns.

=IF(AND(TODAY() >= DATE(YEAR([Cert 1]@row), MONTH([Cert 1]@row), 1), TODAY() <= [Last Day of Grace Period End Month]@row), 1)

NOTE: Ensure you change the columns being referenced as you move through the cert types

Essentially what this is doing is checking the current date and seeing if it is within the 1st day of the starting month and the last day of the ending month and checking the flag if so. This will work if the period range crosses years.

Not sure if this is the best solution but it works.

Hope this helps,

Dave

All comments

Adrien Leduc

First I would say instead of having 26 columns for 26 certifications, how about having one column "certification", possibly a dropdown column so you can input the 26 options?

Then if you need to see the all the certification per individual, you can do this with reports group per assigned column.

This would also help making only one checkbox formula. Then for the formula, I would to something like:

= IF(TODAY()<[Certification expiration date]@row+90,1,0)

It was not clear from your description if you have such a date, but I assume you do?

Lori Flanigan

Thanks, Adrien.

We need to maintain the historical data for each certification, so I'm not sure that combining all into a dropdown field would work for what we need.

Also, we don't have an expiration date at this time.

Paul Newcome

Are you able to provide a screenshot (sample data is fine)?

Lori Flanigan

Paul, here is example data, minus the January-December helper fields.

Smartsheet 90-Day Grace Period Example.xlsx

In the John Smith row, the Cert 2 Grace Current Month checkbox should be checked because it is still April; the Cert 2 Grace Current Month should also be checked because April is within that March-June grace period.

DKazatsky2

Hi @Lori Flanigan,

=IFERROR(DATE(YEAR([Cert 1 Grace End Date]@row), MONTH([Cert 1 Grace End Date]@row) + 1, 1), DATE(YEAR([Cert 1 Grace End Date]@row) + 1, 1, 1)) - 1

NOTE: You will need one of these for each cert type

Next, use the following formula in the checkbox columns.

=IF(AND(TODAY() >= DATE(YEAR([Cert 1]@row), MONTH([Cert 1]@row), 1), TODAY() <= [Last Day of Grace Period End Month]@row), 1)

NOTE: Ensure you change the columns being referenced as you move through the cert types

Not sure if this is the best solution but it works.

Hope this helps,

Dave

Lori Flanigan

Thank you very much, Dave! These formulas definitely work when a grace end date is entered. Even though the IFERROR is wrapping the first formula, I'm still getting #INVALID DATA TYPE errors for each row with a blank grace end date. But, since those are helper columns that I will hide, I'm not concerned. I wrapped the checkbox formula with an IFERROR and that eliminates the #INVALID DATA TYPE from showing in that field.

I so appreciate the help!

Lori

DKazatsky2

I'm glad it's working. If you don't want to deal with the #INVALID DATA TYPE if the end date is blank, you can change the formula in the Last Day column to the following.

=IF(NOT(ISBLANK([Cert 1 Grace End Date]@row)), IFERROR(DATE(YEAR([Cert 1 Grace End Date]@row), MONTH([Cert 1 Grace End Date]@row) + 1, 1), DATE(YEAR([Cert 1 Grace End Date]@row) + 1, 1, 1)) - 1)

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.