#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Options
edited 12/09/19

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!

• ✭✭✭✭✭✭
Options

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
• edited 08/20/15
Options

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.

This discussion has been closed.