Help

Returns a past due status after the date it due

Topic Labels: Formulas
Solved
Jump to Solution
1511 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Jen_Baguhin
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

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

See Solution in Thread

5 Replies 5

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

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
13 - Mars
13 - Mars

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

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.

WOW thanks! :star_struck: