Formula for "Due" and "Past Due"

IF(
	{Due}="",
		"Not Set",
	IF(
		AND(
			DATETIME_DIFF({Due},TODAY(),'hours')>=12,
			DATETIME_DIFF({Due},TODAY(),'hours')<=48
			),
		"Due Soon",
			IF(
				IS_BEFORE(
					{Due},TODAY()
					),
				"Past Due",
			IF(
				IS_SAME(
					{Due},TODAY()
					),
					"Due Today",
				"Upcoming"
				)
			)
		)
	)

This worked, Thank you so much!!!

Could it make it a lot more complicated to add another IF statement that if the Status says “done” then “When is it due” would say Completed instead of Past due?

Nope:

IF( {Status} = "Done", "Completed",
IF(
	{Due}="",
		"Not Set",
	IF(
		AND(
			DATETIME_DIFF({Due},TODAY(),'hours')>=12,
			DATETIME_DIFF({Due},TODAY(),'hours')<=48
			),
		"Due Soon",
			IF(
				IS_BEFORE(
					{Due},TODAY()
					),
				"Past Due",
			IF(
				IS_SAME(
					{Due},TODAY()
					),
					"Due Today",
				"Upcoming"
				)
			)
		)
	)
)

IF( {Status} = "Done", "Completed",
IF(
	{Due}="",
		"Not Set",
	IF(
		AND(
			DATETIME_DIFF({Due},TODAY(),'hours')>=12,
			DATETIME_DIFF({Due},TODAY(),'hours')<=48
			),
		"Due Soon",
			IF(
				IS_BEFORE(
					{Due},TODAY()
					),
				"Past Due",
			IF(
				IS_SAME(
					{Due},TODAY()
					),
					"Due Today",
				"Upcoming"
				)
			)
		)
	)
)

You’re amazing, thank you so much for your help! I’m able to learn a lot through you guys, thank you for your time!

I’m glad to know I could help :grinning_face_with_smiling_eyes: !

IF (
	{Due}="",
	"Not Set",
	IF(
		AND(
			DATETIME_DIFF({Due},TODAY(),'hours') >= 12,
			DATETIME_DIFF({Due},TODAY(),'hours') <= 48
		),
		"Due Soon",
		IF(
			IS_BEFORE({Due},TODAY()),
			"Past Due",
			IF (
				IS_SAME({Due},TODAY()),
				"Due Today",
				"Upcoming"
			)
		)
	)
)

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

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

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.

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

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.

@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!

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

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