Formula for "Due this week" "Due next week" and "Past Due"

Hi @Jessica_Hubbard - this formula does what you are after:

IF(
  {Days till due} < 0, 
  'Overdue',
  IF(
    {Days till due} = 0,
    'Due Today',
    IF(
      {Days till due} <= 7,
      'Due This Week',
      IF(
        {Days till due} <= 14,
        'Due Next Week',
        'Upcoming'
      )
    )
  )
) 

And relies on another field (which could be hidden) Days till Due:

DATETIME_DIFF({Due Date}, TODAY(), 'days')

However, depending upon your definition of “this week” or “next week” it doesn’t feel totally correct. Today is the Sat 4th Jan, so, for me, a task due on the 7th is “next week”, not “this week”.

An alternative approach might be to look at the WEEKNUM of the due date and compare it to the WEEKNUM of “today” (in combination with “days till due”):

{Week} is WEEKNUM({Due Date})

{Week of Today} is WEEKNUM(TODAY())

and the {Alternate formula} is:

IF(
  {Days till due} < 0, 
  'Overdue',
  IF(
    {Days till due} = 0,
    'Due Today',
    IF(
      {Week of Today} = {Week},
      'Due This Week',
      IF(
        {Week of Today} + 1 = {Week},
        'Due Next Week',
        'Upcoming'
      )
    )
  )
)

JB

Jonathan,

Thank you so much! This is exactly what we needed!

-Jessica