What is the best formula to generate expiration 14 days prior

haywoodt

I am trying to figure out a formula that can generate the Renew Date 14 days prior to the "expiration" date.

I was along the path of something like this below, but it is not working.

=DATE[Stop Date]@row - 14

I also would like to know how to add a conditional format that it would trigger the expiring date box to turn red.

Kimberly Loveless

First of all you are close with this formula it should simply be

=[Expiration Date]@row - 14

For the conditional formatting is there set time frame that you were looking to have it turn red like when the expiration date is in the next 14 days?

Steven Stikons

Because the Expiration Date column is already a Date, you can't wrap it in the DATE function.

So in the Renew Date column, enter this formula:

=[Expiration Date]@row -14

Also, regarding the conditional formatting, if you select a Date formatted column for a conditional format, Smartsheet conditional formatting recognizes that and provides "Today" as one of the criteria choices. Select "Today" as the criteria for the Expiration Date column, then select Red in the "apply this format" and select the Expiration Date column in the apply to setting of the conditional format.