Automation

JenaW

I am trying to create a trigger that sends out an email 4 months before. I only see an option for 30 days, 60 days, 90 days, 180 days, and 365.

Matt Molland

You can create a new column with this formula, apply the formula to the entire column. Then use the automated workflow to choose Date Field and select the column you made with these dates.

=IFERROR(IF(MONTH([End Date]@row) < 5, SUM(MONTH([End Date]@row), VALUE(8)) + "/" + DAY([End Date]@row) + "/" + YEAR([End Date]@row-1), MONTH([End Date]@row) - 4 + "/" + DAY([End Date]@row) + "/" + YEAR([End Date]@row)),"")

Replace the Bolded parts with cell you have your dates in.

JenaW

Is there a video I can check out? I am confused with this process.

Paul Newcome

@Matt Molland Your formula is going to output a text string and not a date value. This formula would work to output a date value:

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) - 4, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) - 1, MONTH([Date Column]@row) + 8, DAY([Date Column]@row)))

@JenaW Putting my formula in a date type column, you can leverage this in your automation like so:

JenaW

what values have to be entered into that formula?

Paul Newcome

@JenaW I'm not sure I understand what you are asking.

Matt Molland

@JenaW the values are the part of the formula that are referencing the cell you are pulling the original date from.

1. create a new row you could call "4 months prior" or whatever you want.
2. Copy Paul's formula into the top cell of that new row
3. Replace all parts of the formula that have '[Date Column]@row', by highlighting then click on the original date cell.
4. Hit enter then right click on the formula, then in select 'Convert to Column Formula'

Paul Newcome

@Matt Molland Do you mean "column" instead of "row"?

Matt Molland

@Paul Newcome Yes I did mean Column