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?

[Jeremy_Oglesby]

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!

[Ptt_Pch]

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

[Andrew_Enright]

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

[Jeremy_Oglesby]

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

[Andrew_Enright]

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

[Andrew_Enright]

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

Field 1

IF({Auto-Status} = “Complete”, “ :white_check_mark: ”,
IF({Auto-Status} = “Ghost!”, “ :ghost: ”,
IF({Auto-Status} = “Print Job Ticket”, “ :fax: ”,
IF({Auto-Status} = “Cancelled”, “ :skull: ”,
IF({Auto-Status} = “On Hold”, “ :zzz: ”,
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 :fire: ”,
IF({Days From Release Date} < 0, “Past Due :warning: ️”,
IF({Days From Release Date} = “Due Date Needed”, “Due Date Needed”,
“”))))))))))))))))

[Daniella_Delato]

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?

[Jeremy_Oglesby]

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

[Daniella_Delato]

Jeremy_Oglesby:

IF( {Status} = “Done”, “Completed”,

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!

[Ptt_Pch]

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