Feb 27, 2024

Trigger notification based on cell value that is static (not a date) compared to current date

I have a spreadsheet that tracks applicants for a TA position which includes when the applicant completed TA training. Training times are listed by semester, not date (2023 W, 2022 S, 2023 F - for Winter, Summer, Fall).

TAs must refresh training every two (2) years. Based on the current date, is there a way to trigger a notification when say, it is 2023 Summer and two years have passed since a TA trained in 2021 Summer, so they need to refresh training?

This is what that column looks like: (I can't post the spreadsheet due to FERPA)

Yr/Sem
2022 W          would need training 2024 W
2024 W          would need training 2026 W
2022 F           would need training 2024 F, etc.
2022 F
2023 F
etc.

I can add another column that will show the first day of a semester in date format, if that would help.

Example:

Yr/Sem            Date
2022 W           1/4/2022
2023 S            5/4/2023
2023 F            8/4/2023

Is there any way to do this with a formula or something? 

Note, if the solution is more difficult than a formula (macros, scripts, etc.), I need it explained in laymans terms please.

Locked
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
All Replies
Feb 28, 2024
Hi Linda,

thanks for your question. Are you looking for a cell to the right of the date entered to be populated based on the date in the cell relative to today's date?  This is fairly straightforward to do, and the common challenges are related to the format of the dates.

At a high level, there's a function "=today()" that returns today's date. So you can create a condition where the cell returns a message if the date in the other cell (for this example, say $B$10) took place more than 30 days ago 

​=if(B10<=today()-30,"alert!","")

Happy to help further - please share a sample spreadsheet here with a tab showing how you'd like it to look if the functions were working properly.

Eric
Feb 28, 2024
Hi Eric,

Thank you for your response. I got it to work by changing the -30 to -730 (for two years).

Now that I have the alert, is there a way to make the alert send me a notification?
false
9701962486896497389
true
Search Help Center
true
true
true
true
true
35
false
Search
Clear search
Close search
Main menu