Changing Drop Down Value Based On Different Date Ranges

Shannon Kaposy

Hi All,

Is it possible to make a drop down that will change its value based on a range of dates? I need the drop down to switch between "Past Due" (> today), "This Week" (today +7), "Next Week" (between 7-14 days from today), and "Two Weeks Out" (between 7-21 days from today). Is this possible?

Thanks!

Mark Cronk

Hi Shannon,

Dynamic values in a drop down list aren't possible. However, are you asking to change the value in a drop down column based on dates? That is possible but you don't want to use a drop down. You want to use a text/number column.

You'll use nested IFs to determine the value displayed in the column. The syntax will be:

=IF([due date]@row<today(), "Past due", IF(AND([due date]@row>=today(), [due date]@row<=today(7)), "This Week", IF((AND([due date]@row>today(7), [due date]@row<=today(14)), "Next Week", IF(AND([due date]@row>today(14), [due date]@row<=today(21)), "Two Weeks Out", "More than 2 weeks out"))))

Help?

Mark