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