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?

[Chester_McLaugh]

This will accomplish what you requested.

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

[Hashim_Warren]

Wow @Chester_McLaughlin - you nailed it!

I was able to get “upcoming”, “today”, and “past” working on my own, but “soon” was beyond me.

Also you took into account tasks with no due date! I didn’t know that was possible.

I learned a lot just from seeing this formula work. Thank you!

[Hashim_Warren]

@Chester_McLaughlin how would I modify this so it’s eastern standard time?

[Deez]

I’m starting to get the feeling that there’s more to Airtable than I even understand (like when I read this!). Where would I put that formula??? Sorry to sound so dumb but I’m pretty new at Airtable in general.

[Chester_McLaugh]

Here’s the best place to get familiar with the formula field type.

Airtable - Support

Guide to formula, lookup, rollup, and other computed fields

Computed fields (columns) are special field types where the values in the field are computed by Airtable automatically, rather than directly edited by users. These can be fields that allow you to c...

And here’s a quick glimpse of how to set a field to be of type “formula”

[Chester_McLaugh]

I haven’t tested this, however this should work for your timezone.

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

[Chais_Meyer]

I’m trying to do this formula, replacing {Due} with my field name {Print Due Date}, but I keep getting an error on the formula.

Here’s my video to show you what I’m doing: http://example.24hrtees.net/kL8J

I’d love some help.

[Christoff]

@Chais_Meyer, I had the same problem when I copied and pasted the formula as written by @Chester_McLaughlin.

The layout of the formula is nice because the various IF statements can be identified clearly, along with the corresponding opening and closing parentheses. But, it seems that Airtable has a problem with spaces in the formula. When I removed the spaces, the formula worked fine.

[Kevin_Amsel]

@Chester_McLaughlin Thanks for the code, but i seems to be an error, i replace “Due” with “Deadline” and remove the spaces, but i still have an error in the formula, can you check the code?

Thanks!