Question on Date Alert formula

Options

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.

Best Answers

Answers

  • Dave from JBM
    Dave from JBM ✭✭✭✭✭
    Options

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

  • Richard S
    Richard S ✭✭✭
    Options

    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
    Dave from JBM ✭✭✭✭✭
    Answer ✓
    Options

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



  • Dave from JBM
    Dave from JBM ✭✭✭✭✭
    Answer ✓
    Options

    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
    Richard S ✭✭✭
    Options

    Thank you very much Dave! Really appreciate it!

  • Richard S
    Richard S ✭✭✭
    Options

    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
    Richard S ✭✭✭
    Options

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

    Thank you,

    Richard

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Richard S
    Richard S ✭✭✭
    Options

    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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Richard S
    Richard S ✭✭✭
    Options

    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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!