Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

Topic Labels: Formulas
1088 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

2 Replies 2

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')

Screenshot 2020-01-04 at 02.34.21

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”):

Screenshot 2020-01-04 at 02.46.22

{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