Smartsheet alert 3 days before the first Thursday of the month

Keven

Is there a way w/o having to create a date column and just list the first Thursday of each month manually to send an alert 3 days before. I know I can send one for each 1st Thursday but I need the alert to go off a few days before. Thank you

Paul Newcome

To make sure I am understanding you correctly, you want the next upcoming 1st Thursday of the month to be populated in a date column?

So basically it would currently display 1 July 2021. Then after that it would display 5 August 2021. Automatically updating after each date passes to go on to the next?

Kia Gibbs

@Paul Newcome

Hi Paul,

Though Keven didn't respond, this is a feature I am looking for. I am interested in creating an alert that would be sent out the Monday before each first Thursday. I don't mind creating helper columns or sheet summary columns to make this happen.

If you could assist with this, I would appreciate it!

Paul Newcome

@Kia Gibbs Would you want this based off of today's date, or is there a date column that you would have dates in that youwould want it based on?

Kia Gibbs

@Paul Newcome I am flexible honestly.

I have a "Today's Date" sheet summary cell. I also have a "Meeting Date" column where I record the date of action items assigned during a meeting that occurs every first Thursday of the month.

The goal is to alert meeting attendees on the Monday before each first Thursday to review their action items and mark items as complete or provide an update on an item currently in progress.

Paul Newcome

If you already have a [Meeting Date] column that has the 1st Thursday of the month in it, then you can set up an automation to run daily and then set a condition where this column is 3 days in the future.

Kia Gibbs

@Paul Newcome got it. Thank you! I think I missed the detail from the original post that they would enter the First Thursday dates manually.

I would love to be able to have a formula where the "Next Meeting" date column would change automatically once a meeting date is in the past. Anyways… thanks for all the help and your many contributions!!

Paul Newcome

@Kia Gibbs We could do that with a formula. Would you want it to basically show the next upcoming "first Thursday" based on today's date?

Kia Gibbs

@Paul Newcome Yes, that would work!

Paul Newcome

@Kia Gibbs Give this a shot:

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH(TODAY()), 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))), 1, 0), 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - IF(TODAY() > DATE(YEAR(TODAY()), MONTH(TODAY()), 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))), 11, 0), 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))))

Kia Gibbs

@Paul Newcome

Hi Paul! Apologies for the delayed response. Thank you so much for the formula!!!

I entered the formula today (4.16.2025) but the value returned was 5.3.25 (a Saturday).

I love Smartsheet… but I believe there are some basic features that I believe could be implemented.

Paul Newcome

How does this one do?

=IF(TODAY() > DATE(YEAR(TODAY()), MONTH(TODAY()), 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1))), DATE(YEAR(TODAY()) + 1, 1, 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()) + 1, 1, 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()) + 1, 1, 1)))), DATE(YEAR(TODAY()), MONTH(TODAY()), 1) + (IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) <= 5, 5, 5 + 7) - WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1))))