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

[Jessica_Hubbard]

Hello!

I am trying to build out a formula that will allow for the following fields to populate based off of a due date:

“Due this week” within 7 days of due date
“Due next week” 8-14 days of due date
“Upcoming” due after 15 days
“Due Today”
“Overdue”

I have reworked my formula over and over for the past 4 hours and can not seem to get it to work. Any help would be much appreciated!

Thanks!
Jessica

[JonathanBowen]

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

[Jessica_Hubbard]

Jonathan,

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

-Jessica