Returns a past due status after the date it due

[Jen_Baguhin]

IF(
AND(
{Date target goals to be published*} <= NOW(),
{Link Building Status} = “Posted/Scheduled”
),
“Live”,
IF(AND({Date target goals to be published*} < NOW(),
{Link Building Status} != “Posted/Scheduled”),
“Past Due”, IF(AND({Date target goals to be published*} > NOW(),
{Link Building Status} = “Posted/Scheduled”), “Scheduled”)
)
)

Hi I am using this formula. And it works well except for 1 thing.

It is returning “past due” on the field 12 on the same day it is due. Is there anyway to make it return a result of past due after the actual day of due.

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[JonathanBowen]

Hi @Jen_Baguhin - There are two issues. The first is that your final IF statement is missing its final argument. I think it should be:

IF(
  AND({Date target goals to be published*} > DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'), {Link Building Status} = "Posted/Scheduled"), 
  "Scheduled",
  ""
  ) 

Note the empty quotes at the end.

The main issue though is that NOW() includes a time component so NOW() (right now) is, for me:

4/16/2019 9:55am

A simple date has an implied time, I think, of 4/16/2019 00:00 which will always be less than the same date with a time component. So you need to format NOW() to exclude the time component using:

DATETIME_FORMAT(NOW(), 'YYYY-MM-DD')

So your full formula should be:

IF(
  AND({Date target goals to be published*} <= DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'), {Link Building Status} = "Posted/Scheduled"),
  "Live",
  IF(
    AND({Date target goals to be published*} < DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'),{Link Building Status} != "Posted/Scheduled"),
    "Past Due", 
    IF(
      AND({Date target goals to be published*} > DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'), {Link Building Status} = "Posted/Scheduled"), 
      "Scheduled",
      ""
      )
  )
)

JB

[Jen_Baguhin]

https://take.ms/AsT9n

I noticed that it will be using past due even there is no set date for it.

I tried adding another formula from what you’ve made…

IF(
  AND({Date target goals to be published*} <= DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'), {Link Building Status} = "Posted/Scheduled"),
  "Live",
  IF(
    AND({Date target goals to be published*} < DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'),{Link Building Status} != "Posted/Scheduled"),
    "Past Due", 
    IF(
      AND({Date target goals to be published*} > DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'), {Link Building Status} = "Posted/Scheduled"), 
      "Scheduled",
	IF(
      AND({Date target goals to be published*} =“” , {Link Building Status} = ""), 
      "",
      ""
	)
      )
  )
)

But it is giving me an error

[JonathanBowen · Accepted solution]

OK, so it just needs to first check if a date is in the record, so:

IF(
  {Date target goals to be published*},
  IF(
    AND({Date target goals to be published*} <= DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'), {Link Building Status} = "Posted/Scheduled"),
    "Live",
    IF(
      AND({Date target goals to be published*} < DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'),{Link Building Status} != "Posted/Scheduled"),
      "Past Due", 
      IF(
        AND({Date target goals to be published*} > DATETIME_FORMAT(NOW(), 'YYYY-MM-DD'), {Link Building Status} = "Posted/Scheduled"), 
        "Scheduled",
        ""
        )
    )
  ),
  ""
)

JB

[Justin_Barrett]

JonathanBowen:

There are two issues. The first is that your final IF statement is missing its final argument.

The documentation doesn’t mention this, but the final argument of an IF function is optional. If omitted, it defaults to an empty string, or zero if the function returns a numerical value when true.

[Jen_Baguhin]

WOW thanks! :star_struck: