The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

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

Topic Labels: Formulas
1958 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

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
13 - Mars

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

4 - Data Explorer

Jonathan,

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

-Jessica