Feb 27, 2024
Trigger notification based on cell value that is static (not a date) compared to current date
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.
Details
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
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
Eric
Feb 28, 2024
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?