Question on Date Alert formula

Richard S

Hi all,

First time posting and fairly new to Smartsheet when it comes to formulas and automations.

Can someone help with a date alert formula or can I do it in automations?

Let's use as something as a drivers license for example.

I am keeping a long list of people on when I receive their license and also recording the date on when it expires.

What I would like to do is set up color coding in the cell as we get closer to the expiration date and an alert each time the expiration date is met.

Yellow for 6 months

Red for 3 months.

Additional i would like email/alert to be sent when hitting these warnings.

Appreciate your help. Thank you

Richard S.

Dave from JBM

Here is an example of conditional formatting for this type of application:

Dave from JBM

Here is the automation to alert those managing the sheet. You could create a second one where instead of 180 you list 90 for the 90 day alert but the way this is setup it will send an alert every Friday on anyone that is currently under 180 days to expiration:

Dave from JBM

Richard, Does the alert need to go to you or the person with the drivers license?

Richard S

Hi Dave,

The alert would go to 2 people that is managing the sheet but not to the individual with the license.

Thanks,

Richard

Dave from JBM

Here is an example of conditional formatting for this type of application:

Dave from JBM

Here is the automation to alert those managing the sheet. You could create a second one where instead of 180 you list 90 for the 90 day alert but the way this is setup it will send an alert every Friday on anyone that is currently under 180 days to expiration:

Richard S

Thank you very much Dave! Really appreciate it!

Richard S

Hi, I have another question that is similiar.

Is there way to setup automation expired date?

Let say I have a column that states "Issued Date" and another column that is the "Expired Date"

I want to expired date to auto generate from the issued date entered.

So if i entered 3/18/22 and I wanted the expired date to enter a date 2 years from now 3/18/24, is that possible? I want to keep the same logic as above as well which should not change I would think as it will still be based on the expired date.

Thanks,

Richard

Richard S

Hi, just following up on my question above and if any know?

Thank you,

Richard

Genevieve P.

Hi @Richard S

I would personally build this out by using a formula to generate the Expired Date. You can simply add days to a date to create another date, so what you could do is have a "Days" column in your sheet between your two date columns.

Then, after you enter the first date, enter in the number of days until it expires (ex. 730 is two years).

Your "Expired Date" column could then add these two cells together to get the new date:

=[Issued Date]@row + [Days Column]@row

Keep in mind that the formula would need to be entered into a Date type of column to produce a date.

See: Use Formulas to Perform Calculations With Dates

Cheers,

Genevieve

Richard S

Thanks for your help Genevieva.

Unfortunately, formulas are my week point but trying to learn. Been trying to figure this one out for the last hour but keep coming up with errors. I have a lot of reading to do to understand how formulas work, very complicated stuff....

Genevieve P.

Hi @Richard S

Here's an image of what I was describing above:

You'll need to make sure your columns are the correct type (see the blue header row). Then you can simply add the Date and the Number together to create a new Date!

If you need more help, it would be useful to see the formula you're working with, and a screen capture of your sheet with the formula open (as above), but please block out sensitive data.

Cheers,

Genevieve

Richard S

Thank you Genevieve, that worked!

See below example. How would you make the counter automate once the issued date entered?

I want to be able to just enter the Issued Date and Expiration Date just automates the expired date based on the days I set it to.

So the Days counter would be hidden. I would just enter the Issued date and would auto the expiration date. I would set this automation for 730 days and 1095 days.

Is that possible?

Genevieve P.

Hi @Richard S

It is possible! But only if there's something on the row that lets Smartsheet know if you want it to be 730 or 1095. For example, some part of a text in the description or a type of license number, etc. The sheet can only automatically add in information based on a set of rules that you configure.

What I would do in this instance is set up a Change Cell Workflow that will automatically add 730 into the Days cell or 1095 when a new row is added, based on specific conditions.

See: Change the Value of a Cell in an Automated Workflow

Cheers,

Genevieve