Upcoming or Overdue deliverables

Hi - I am trying to create a filter or a formula that basically returns items that are overdue or upcoming (i.e. due within the next 21 days). To qualify as overdue, that status would have to be “Late”. To qualify as an upcoming item, the status should not be “Complete” or “Duplicate” AND Due should be btwn 0 and 21 days. I’ve spent hours on this with multiple formulas and columns. The filter on the board doesn’t work either b/c I can’t use a combination of AND and OR in the board filter. It has to be one or the other. The ugly way I got it to work was create 3 formula columns:

  • LATE with this formula: if({Status}=“Late”, “LATE”, “IGNORE”)
  • UPCOMING with this formula: IF(AND({Status} <> “COMPLETE”,(Days({Due},today())<=21)),"less than 21 days; ","complete or over 21 days ; ") & IF(AND({Status} <> “COMPLETE”,(Days({Due},today())>=0)),“greater than 0 days”, “complete or less than 0 days”)
  • OVERDUE or UPCOMING ITEMS = if(or({Late}=“LATE”, {Upcoming}= “less than 21 days; greater than 0 days”), “OVERDUE or UPCOMING”, “NOT a candidate”)
    Then create a board filter that filters on OVERDUE or UPCOMING ITEMS column is “OVERDUE or UPCOMING”

I tried other really complicated nested ifs but nothing else has worked. I feel like there must be a simpler way to do this than the ugly way I got it to work. Hoping someone in the community can help :slight_smile:

Thank You!

hi @SSplaycore

Welcome to the community! I am not a formula expert but I am sure somebody can help you with a formula solution. Be reminded that the outcome of a formula can’t be used in other automations. For the purpose of upcoming and overdue deliverables you can also think of an “Auto Group move”. With this app monday.com: Apps Marketplace) all items will be placed in groups as you define them. The app runs on a user specified schedule. Example groups are:

  • due in 3 weeks
  • due in 2 weeks
  • due in 1 week
  • due tomorrow
  • overdue by 1 day
  • overdue by 1 week
    etc