Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Formula for "Due" and "Past Due"

4438 0
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

36 Replies 36

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

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!

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

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.

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

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

7186a17047d0e2f770ecabb09de9d16b3cfe5df2.gif

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
6 - Interface Innovator
6 - Interface Innovator

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.

@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
4 - Data Explorer
4 - Data Explorer

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