Error with nested IF statements containing date fields

#1

Hello!
I am relatively new to Airtable and am trying to figure out why my nested IF statement isn’t working.

Goal: If the due date field value is before today and the field complete is not checked show “in progress”, if complete is checked then show todays date if none of the above are true show “missed”.
Current Formula:
IF({Due Date} IS_BEFORE({Due Date}, TODAY()),“In progress”, IF(Complete=1 ,DATETIME_FORMAT(TODAY(),“l”),“Missed”))

I may be using the wrong formula as well. I tried a few variations and couldn’t get it to work. Any help would be great! Thanks!

This issue seems similar to this but not quite the same: Problem in my Nested IF-THEN-ELSE statement

#2

2 ideas:

  • The date before IS_BEFORE() isn’t needed, it is already in the parameters.
  • You can use AND() to evaluate 2 conditions.
#3

Hey Elias,

Thanks for the info! I have two questions about those ideas.

  • How is the “IS BEFORE” built into the parameters? If I removed that, my assumption is that it would just evaluate if it is today or not today rather than giving me the ability to have 3 phases of Date completed, in progress, and missed based on a combo of the completed check, the due date, and today’s date.
  • For the AND function am I nesting this?

I tried using this formula to return text strings that I could then use in tandem with a “Complete Yes/No” field but am still receiving an error. Again, could be dumb formatting on my part I’m not sure.

IF({Due Date} = TODAY(), "Due Today"), IF({Due Date} IS_BEFORE(TODAY(),"In progress"), "Missed")

Thanks!

#4

About the is_before

The format is IS_BEFORE([date1], [date2]).

Determines if [date1] is earlier than [date2]. Returns 1 if yes, 0 if no.

  • Human language: “Is 12th January before today?”.
  • Formula: IS_BEFORE({12th January},TODAY()).

You don’t need to write the evaluated date before the command, and that returns TRUE. Of course the date format is not valid, it’s just an example.

So if you want to test that in an IF, it goes:

IF(IS_BEFORE({1 January},TODAY()),"It's the past",'Today or the future').

Options

I have this options:

What happens if is not before, and is completed?

The formula

I don’t understand your statues, but here it is, as working in my demo table:

IF(
	IS_BEFORE(
		Date,
		TODAY()
	),
	IF(
		Complete,
		DATETIME_FORMAT(
			TODAY(),
			'll'
		),
		'In Progress'
	),
	IF(
		Complete,
		'????',
		'Complete'
	)
)

Oh, I think you don’t need the AND() function, but it works like this: AND(logical1, [logical2, ...]).

Anyway, you can change the returning string to whatever you want, but that is the structure for 2 options and its 4 possible variations.

#5

This is super helpful. I was able to get what I needed with two separate formulas shown below:

  1. IF({Due Date} >= TODAY(), "In progress", "Missed")
  2. IF(Complete = 1, DATETIME_FORMAT(TODAY(),"l"), {Completed Date (Dependent Field)})

I think your solution is an elegant way to consolidate what I have achieved above into a single formula. Thanks again!

#6

I hope this helps