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!

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"


			)


		)


	)


)

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

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"


			)


		)


	)


)

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”

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"


			)


		)


	)


)

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.

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

You’ve probably already figured this out, but as a breadcrumb for future searchers, it’s the two spaces following IF on lines 1 and 13 causing the problem; there should be no space between ‘IF’ and the opening parenthesis, as so: IF(. The rest of the white space – which, as @Christoff points out, makes the code much easier to read, can stay; Airtable ignores it.

The problem is “”, if you look carefully when you copy from web and past to the airtable, it generate to another character not same with the " understand in airtable. Hope it help you. It took me ages to debug and look carefully.

@Chester_McLaughlin This worked perfectly for me once I deleted the additional spaces. Now I am wondering if I can add one more piece to the formula. This would be based on another Checkbox Column (separate from the Due Column). In other words, if the checkbox column is checked, I want the Report Status to say “Received” instead of “Past Due”. Is that possible?

Certainly. Here’s the basic format for checking the status of a checkbox field:

IF({My Checkbox Field}=TRUE(),"It is checked!!!","It is NOT checked")

And if you ever want to check the inverse, do:

IF({My Checkbox Field}=FALSE(),"It is NOT checked","It is checked!!!")

Or you could do:

IF(NOT({My Checkbox Field},TRUE()),"It is NOT checked","It is checked!!!")

Thanks, @Chester_McLaughlin Chester. I was able to make this work when I made a new column. But


I want to imbed this into the original formula from the thread. Is that


possible? So in one column, there would be either Received, Upcoming, or


Past Due. Upcoming and Past Due are generated from the datetime diff and


Received would be like an override if I select the checkbox in that row.

IF(


	{Received}=TRUE(),


	"Received",


	IF(


		{Deadline}="",


		"Not Set",


		IF(


			AND(


				DATETIME_DIFF(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') >= 12,


				DATETIME_DIFF(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') <= 48


			),


			"Due Soon",


			IF(


				IS_BEFORE(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),


				"Past Due",


				IF (


					IS_SAME(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),


					"Due Today",


					"Upcoming"


				)


			)


		)


	)


)

IF(


	{Received}=TRUE(),


	"Received",


	IF(


		{Deadline}="",


		"Not Set",


		IF(


			AND(


				DATETIME_DIFF(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') >= 12,


				DATETIME_DIFF(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York'),'hours') <= 48


			),


			"Due Soon",


			IF(


				IS_BEFORE(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),


				"Past Due",


				IF (


					IS_SAME(SET_TIMEZONE({Deadline},'America/New_York'),SET_TIMEZONE(TODAY(),'America/New_York')),


					"Due Today",


					"Upcoming"


				)


			)


		)


	)


)

@Chester_McLaughlin This did it. Very cool! Thanks a lot.

Looks like I’m about a year late to the party but the code still doesn’t work for me


I deleted all spaces and re-typed all "


Any other tips on how I could make this work?

What happens to me a lot when working on formulas is I accidentally delete or forget a a comma and/or a bracket :grinning_face_with_sweat: .


It may be simply it.

Could you copy/paste here your corrected formula to see if … ? :winking_face:

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 you notice, there are 2 different types of double-quotes in the formula you pasted there - the “straight” kind after the initial “Due=“, and the “curved” kind throughout the rest of the formula.

I don’t know what the technical difference is, or where the difference comes from, frankly, but I’d suggest just going through the formula in your Airtable field and replace every double-quote in it with your double-quote keystroke. If that doesn’t work then I’m not sure what could be wrong, because the rest looks fine!

@Daniella_Delatorre

I agree with @Jeremy_Oglesby :winking_face:

But I think you forgot to close your IF() too …

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"


				)


			)


		)


	)

Other than that, Without knowing how your table looks like, I don’t see anything else :woman_shrugging:

Agreed, when moving formulas between text editors it’s easy to pick up “smart” or “curly” quotes. These must be reverted to “dumb quotes” for the formulas to parse correctly. This trips me up all the time, and may be a common snag in design shops that actually prefer smart quotes in most use-cases.

Smart Quotes: “ ”

Dumb Quotes: " "

Is there an actual keystroke difference there? Or is it just a difference in the engine that interprets your keystroke?

It likely depends on each users OS and/or text editor. Many default to smart quote auto-substitution.

But yes, you can specify the Unicode character used via keystroke:

https://practicaltypography.com/straight-and-curly-quotes.html

And FWIW, here’s our team’s (two-field) formula for Due/Past Due:

Field 1

IF({Auto-Status} = “Complete”, “ ”,


IF({Auto-Status} = “Ghost!”, “ ”,


IF({Auto-Status} = “Print Job Ticket”, “ ”,


IF({Auto-Status} = “Cancelled”, “ ”,


IF({Auto-Status} = “On Hold”, “ ”,


IF({Release Date} = 0, “Release Date Needed”,


DATETIME_DIFF({Release Date}, TODAY(), ‘days’)


))))))

Field 2

IF({Days From Release Date} > 12, “Soon”,


IF({Days From Release Date} = 12, “Next Week”,


IF({Days From Release Date} = 11, “Next Week”,


IF({Days From Release Date} = 10, “Next Week”,


IF({Days From Release Date} = 9, “Next Week”,


IF({Days From Release Date} = 8, “Next Week”,


IF({Days From Release Date} = 7, “Next Week”,


IF({Days From Release Date} = 6, “This Week”,


IF({Days From Release Date} = 5, “This Week”,


IF({Days From Release Date} = 4, “This Week”,


IF({Days From Release Date} = 3, “In Three Days”,


IF({Days From Release Date} = 2, “In Two Days”,


IF({Days From Release Date} = 1, “Tomorrow”,


IF({Days From Release Date} = 0, “Today ”,


IF({Days From Release Date} < 0, “Past Due ️”,


IF({Days From Release Date} = “Due Date Needed”, “Due Date Needed”,


“”))))))))))))))))