Skip to main content
Solved

Returns a past due status after the date it due

  • April 16, 2019
  • 5 replies
  • 39 views
J

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.

Best answer by JonathanBowen

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

5 replies

JonathanBowen
Forum|alt.badge.img+18

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

J
  • Jen_Baguhin
  • Author
  • New Participant
  • April 16, 2019

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
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • Answer
  • April 16, 2019

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

J
A
Justin_Barrett
Forum|alt.badge.img+21

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


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.

J
JonathanBowen
J
  • Jen_Baguhin
  • Author
  • New Participant
  • April 16, 2019

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:

Terms & ConditionsAccessibility statement