Updating Date with new year.

Joan B

If Inspection Due Date is required every year in October and this year the inspection did not take place until December, how can I write a formula to send reminder every year 90 days before required date of October, not December. And how do I get the required October date to update with each new year so the automation always sends 90 days before October?

Columns are:

1. Required Inspection Date
2. Actual Inspection Date
3. Automation - 90 days before Required Inspection Date, send reminder. (This will not send every year since date in Required Inspection Date is current year)

Thank you!

Jason P

Hi Joan,

"Mmmmm, Automoation you seek, formula it is not" (inner Yoda)

Use send an Alert Automation and set the Run Once to 90 days before. In the example below the reminder is set on the date column Order date.

Hope this helps.

Joan B

Hi Jason... thank you for your quick reply. This automation works great for this year (ex: 90 days before 12/1/2024 will send reminder in September) but how does the date field automatically update each year so the automation knows to send 90 days before the same date in 2025, 2026, etc?

Jason P

Hey Joan,

It wont, you will need to update OR. What about setting up another column and putting the Automation on that?

In my example we have Actual Inspection Date & Next inspection date, the latter being the date column you put the 90 day alert on & formula below. Once pasted, right click he cell and convert to a Column formula, this way all new entries are captured.

=IF(ISBLANK([Actual Inspection date]@row), "", DATE(YEAR([Actual Inspection date]@row) + 1, MONTH([Actual Inspection date]@row), DAY([Actual Inspection date]@row)))

This fomular simply adds 1 year to the original date, uses original month and keeps original date except for a leap year where is pushes +1 day. =IF(ISBLANK([Actual Inspection date]@row), "" results in a blank cell where there is no date in Actual Inspection date cell.

Hope this helps?

KPH

Another option is to use the custom recurrence option in the automation to alert on the same date each year. You can enter the next alert date (Oct 2025) as the start date and have this repeat annually on that date.

Jason P

Morning @KPH

I looked at this but my understanding of this occurance is that would apply to all rows being alerted on that one date?

KPH

You can use a condition to limit the rows included in the alert.

Paul Newcome

You can use a formula to output a specific date each year that automatically updates each year.

=DATE(YEAR(TODAY()) + IF(TODAY()> DATE(YEAR(TODAY()), 12, 1), 1, 0), 12, 1)

Change each 12 to the month you want and each 1 to the day. If you want to use helper columns for cell references so you can simply adjust a cell on each row (and allow for a column formula) instead of adjusting the formula on every row, you can do that too.

=DATE(YEAR(TODAY()) + IF(TODAY()> DATE(YEAR(TODAY()), [Month Column]@row, [Day Column]@row), 1, 0), [Month Column]@row, [Day Column]@row)

Jason P

@Joan B

Spoilt for choice....

KPH

😀 Joan's company will never miss an inspection again.