Nested IFs to populate Priority based on set number of days prior to start date

I'm trying to populate a Priority column with Urgent (within 1 week from today), High (1 - 3 weeks from today), Med (3 - 4 weeks from today), Low (4+ weeks from today), Unknown (no date provided) based on the # of weeks out for the start date of the effort.

Both of these work for just 1 Date Range:

=IF({Sheet Range 9} <= TODAY(7), "Urgent", "TBD") -> Reference to separate sheet

=IF(NETDAYS([Column19]@row, TODAY()) <= 7, "Urgent", "TBD") -> Reference within existing sheet

Here's what's not working to get this to populate Urgent, High, Med for multiple Date Ranges. I didn't try to add Low or Unknown since I'm already struggling with the nested IFs.

=IF({Sheet Range 9} <= TODAY(7), “Urgent”, IF(AND({Sheet Range 9} > TODAY(+7), {Sheet Range 9} <= TODAY(+21)), “High”, “Med”))

Best Answer


  • Whoa, who would have thought that would matter! That fixed the issue, now I just have to figure out the rest of the nested IFs.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!