If formula to provide forecast categories

Juan Pimentel

I'm trying to create a formula that will give help identify when future tasks will begin based on the Start date. The formula must provide whether the task is more than 30 days as "Inventory", less than 30 days from starting as "Forecast", or within 5 days of starting as "Upcoming". Below is the formula I came up with but it doesn't quite work. For example, there are some records where the start date is 16 days out but the formula is not showing anything, or when the task is starting in 6 days but its showing "Inventory" instead of "Upcoming".

=IF(AND([Start Date]@row > TODAY(+30), Status@row = "Not Started"), "Forecast", IF(AND([Start Date]@row <= TODAY(+5), Status@row = "Not Started"), "Upcoming", IF(AND([Start Date]@row <= TODAY(+30), Status@row = "Not Started"), "Inventory", "")))

dojones

Your formula is out of order. Start with the condition closest to today first. Try this.

=IF(AND([Start Date]@row <= TODAY(+5), Status@row = "Not Started"), "Upcoming", IF(AND([Start Date]@row < TODAY(+30), Status@row = "Not Started"), "Forecast", IF(AND([Start Date]@row >= TODAY(+30), Status@row = "Not Started"), "Inventory", "")))

dojones

Your formula is out of order. Start with the condition closest to today first. Try this.

=IF(AND([Start Date]@row <= TODAY(+5), Status@row = "Not Started"), "Upcoming", IF(AND([Start Date]@row < TODAY(+30), Status@row = "Not Started"), "Forecast", IF(AND([Start Date]@row >= TODAY(+30), Status@row = "Not Started"), "Inventory", "")))