Automation - triggered within a set number of days before a date

Question

Hi everyone!

I need to set up an automation that is triggered within a set number of days before the date listed in a date field. I need the automation to send an alert email to a new hire at most 7 days before the start date.

I know I can set the automation to send 1 week before the start date, but if the new hire's details aren't added to the sheet until 5 days before the start date, for example, will the trigger still work?

Basically, I'm trying to figure out of the "1 week before" is absolute and will only send at 1 week before the start date, or if it will also send if the start date is within 1 week before the start date.

TVang · Answer

An alternative is to have the automation run every day, and only send an email (only one time) when the new hire is to start within the next 7 days. REQUIRED COLUMNS [Start Date] …date column type [Current Date] …date column type (use this instead of the TODAY() function) [Date Alert Sent] …date column type [Number of Days to Start Date] …text/number column type [Number of Days to Start Date] =IF([Start Date]@row = "", 999, IF([Current Date]@row = "", NETDAYS(TODAY(),[Start Date]@row), NETDAYS([Current Date]@row, [Start Date]@row))) WORKFLOW TO SEND ALERT Trigger: "Every day starting on..." at <> Conditions: Where [Number of Days to Start Date] "is less than or equal to" 7 and where [Date Alert Sent] "is blank" Action 1: Alert someone Action 2: Record a date in: [Date Alert Sent] WORKFLOW TO RECORD CURRENT DATE Trigger: "Every day starting on..." at "12:00 AM" Action: Record a date in: [Current Date]

Melissa Yamada · Answer

“1 week before” is tied to the date, not to when the row is created.

In practice, that means the workflow only fires when the sheet hits that exact point in time (7 days before the Start Date). If the row doesn’t exist yet, or the Start Date is added after that moment has already passed, the automation won’t “catch up” and send late. So in your example, if the row is created 5 days before the Start Date, the 1-week trigger has already been missed and no alert will go out.

If you need this to work reliably for late entries, maybe try to layer in a second workflow that triggers on row creation or when the Start Date is added/changed, with a condition like “Start Date is in the next 7 days.” That way:

the scheduled workflow handles the ideal case (exactly 7 days before)

the second workflow catches anything that was added inside that 7-day window

Just make sure to test your trigger and conditions, so that some rows don't unintentionally get both notifications.