Formula for "Due" and "Past Due"

[Hashim_Warren]

I have a table of tasks. Each task has a due date in a date field.

I want to create a new formula field that looks at the due date and spits out:

Upcoming
Due Today
Past Due

Extra credit if the formula can spit out “Due Soon” when a task is 2 days away.

How do I make this happen?

[Aric_Crosson_Bo]

This is a little of an old thread, but anyone looking for a more fleshed out solution, this is what i’ve come up with. I’ve included numbers in the output so that using the “sort” or “group” functions, they sort in the order i want. I probably could have ordered my IF statements more in line with the numbering too, but it gets confusing if I edit it too much (i’m fairly new to this!) Anyway, you should be able to simply copy and paste this into an Airtable formula, after setting a “Due” Date-type field. Here it is:

IF(
	{Due}="",
	"7.Not Set",
	IF(
		AND(
			DATETIME_DIFF({Due},TODAY(),'hours') >= 12,
			DATETIME_DIFF({Due},TODAY(),'hours') <= 48
			),
		"3.Due Soon",
		IF(
			AND(
				IS_BEFORE({Due},TODAY()),
				{Progress Status}!="Markups Complete - Ready for Review"
				),
			"1.Past Due",
			IF(
				{Progress Status}="Markups Complete - Ready for Review",
				"6.Complete",
				IF(
					IS_SAME({Due},TODAY()),
					"2.Due Today",
					IF(
						WEEKNUM({Due})=WEEKNUM(TODAY()),
						"4.This Week",
						IF(
							WEEKNUM({Due})=(WEEKNUM(TODAY())+1),
							"5.Next Week",
							"In The Future"
						)
					)
				)
			)
		)
	)
)

[Kitty_Goodwin]

Thank you so much from the future :joy: this helped me fix it!

[Jessica_Perez]

I was able to successfully use this code but now I’m getting a “Past Due” alert for projects that have a status of complete. Is there a way to trigger a new status of “Completed” in the place of “Past Due” that is dependent on a field that is not the date field? In my table I use a Due Date Status column (where this code is located) and Project Status column that is updated by the user.

[Shenghong_Zhong]

it’s the problem with quotation. you have to change in airtable? even for text value. like “due date”…

[ryan_wilcox]

I have also managed to use this formula for a product management airtable - thanks. What is working is a column that uses formulas against the UAT estimate date (no estimate yet, upcoming, due soon and due today). However I need to add into the same column a way of checking the date delivered column to show a “done on time” status if date delivered is less than UAT estimate or “Delivered late” status if date delivered is greater than UAT date.

[Kelly_Smith]

@Jeremy_Oglesby I have a similar question but I want something like: IF({Due date} > TODAY(), AND {Status} != “Complete”, “PAST DUE”) (please pardon my toddler-babble!) Thank you!

[Kamille_Parks]

Proper usage of the AND() function is show further up in this thread.

IF(AND({Due date} > TODAY(), {Status} != "Complete"), "PAST DUE")