What formula to add to a "Days Remaining" field to pull from a date box

Drew Ehrlich

Hello,

 

I am trying to create a formula for a column that will determine the number of days remaining from an expiration date.

 

Furthermore, if possible, I would like a status indicator (green, yellow or red button) to remain statically green until the "Days Remaining" field gets down to 30 days remaining.  At this point, I'm hoping there is a formula to change the button to yellow.  When days remaining reaches zero (or goes negative), I would like the button to turn red.

 

Any help would be greatly appreciated!

Tim Meeks

Drew,

 

I think this type of formula would work based on what you explained. (see below.)

Yours would be ={expiration date}1-TODAY() 

 

Then use conditional formatting to change the field to yellow or red.

http://help.smartsheet.com/customer/portal/articles/516359-conditional-formatting

 

You would need 2 rules. One that states if the Days Remaining is less than 30 turn the cell yellow.  Another if the day is less than or equal to = 0, turn the cell red.

Put the Red rule in front of the yellow rule so it runs first.

 

http://help.smartsheet.com/customer/portal/articles/775363-using-formulas#date

 

hope that helps.

Tim

 

TODAY(): Returns the current date.

Displays today's date in a Date type column. When used in other formulas, it can be used to compare dates with today.

• Example: =[Due Date]1 - TODAY()
• Result: The number of days between today and 11/01/13

John Hammond

Try this formula:

 

=IF((Date3 - TODAY()) < 1, "Red", IF((Date3 - TODAY()) < 30, "Yellow", "Green"))

 

This will show green if the date is more than 30 days in the future, yellow if its less than 30, and red if it is 0 or negative.