# Automation

Options
✭✭
edited 09/15/23

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.

• ✭✭✭
edited 09/15/23
Options

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.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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:

• ✭✭
Options

what values have to be entered into that formula?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

@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'

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

@Paul Newcome Yes I did mean Column

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!