Skip to main content

Nested IF statements gives blank false result instead of text

  • July 30, 2018
  • 2 replies
  • 56 views
J

For some reason, if all these conditions fail I’m just getting a blank field instead of the correct “default” status I’m looking to display. Here’s what I’m trying to do…

I’m creating a client “hit list” of sorts, with dropdown status options as the current relationship I have with trying to get work from them. So those options are:

Not Interested
No Work Available
Possible Work Available
Work Available
Booked

I also have a last contacted date column. So what I want to do is have each one have a different follow-up date based off the date last contacted using the DATETIME_DIFF function, with a 120-day follow-up if they’re not interested, 45-days if no work is available, 10-days if there’s possible work available, 5-days if there’s work available, and booked should also return false, or go to the default message which I’m using “Standing by…” for now. However, if I don’t have a value that’s true (i.e. if a row has “Possible Work Available” and is NOT more than 10 days since last contact, I’m just getting a blank field. Here’s my current formula:

IF({Status}=“No Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>45,“Check-in for available work”),
IF({Status}=“Possible Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>10,“Follow up on possible work”),
IF({Status}=“Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>5,“Follow up on job discussed”),
IF({Status}=“Not Interested”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>120,“Reach Out Again”),
“Standing by…”))))

So if any of those are false, I’m just getting a blank field instead of “Standing by…”, if they’re TRUE they are displaying the correct text so at least those are working. But I’d love to have a filter with the “Standing by…” text so I need something to display if all else is FALSE.

I also tried returning alternate text for each Status if the value wasn’t correct, but was getting an error when I used the following formula (changed the due date slightly to allow for the > and < variations):

IF({Status}=“No Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>44,“Check-in for available work”),
IF({Status}=“No Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},'days’)<45,”Standing by…“),
IF({Status}=“Possible Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>9,”Follow up on possible work"),
IF({Status}=“Possible Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},'days’)<10,”Standing by…“),
IF({Status}=“Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>4,“Follow up on job discussed”),
IF({Status}=“Work Available”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},'days’)<5,”Standing by…“),
IF({Status}=“Not Interested”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},‘days’)>119,”Reach out again, updated reel“),
IF({Status}=“Not Interested”,IF(DATETIME_DIFF(TODAY(),{Last Contacted},'days’)<120,”Waiting for 3 months…“),
"Standing by…”))))))))

Somehow the ending “false” text isn’t showing up when the other conditions fail, did I miss something? I tried stringing them together with a “+” or an “AND()” statement but it wasn’t working. Also, if I don’t select ANYTHING in the “Status” column, it DOES display “Standing by…”, but that’s of no use if I have a blank row.

    This topic has been closed for replies.

    2 replies

    C
    Forum|alt.badge.img+18

    Give this a try:

    IF(
	AND( {Status} = "Not Interested", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 120 ),
	"Reach Out Again",
	IF(
		AND( {Status} = "No Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 45 ),
		"Check-in for available work",
		IF(
			AND( {Status} = "Possible Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 10 ),
			"Follow up on possible work",
			IF(
				AND( {Status} = "Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 5 ),
				"Follow up on job discussed",
				"Standing by..."
			)
		)
	)
)
    J
    • Jeremy_Smolik
    • Author
    • New Participant
    • July 31, 2018

    Give this a try:

    IF(
	AND( {Status} = "Not Interested", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 120 ),
	"Reach Out Again",
	IF(
		AND( {Status} = "No Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 45 ),
		"Check-in for available work",
		IF(
			AND( {Status} = "Possible Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 10 ),
			"Follow up on possible work",
			IF(
				AND( {Status} = "Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 5 ),
				"Follow up on job discussed",
				"Standing by..."
			)
		)
	)
)

    That fixed the blank FALSE display, thanks Chester! I tried also adding the same function to display a different FALSE for each Status type, but I think it conflicts somehow:

    IF(

    AND( {Status} = "Not Interested", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 120 ),

"Reach out again, updated reel“,

IF(

	AND( {Status} = "No Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 45 ),

	"Check-in for available work",

	IF(

		AND( {Status} = "Possible Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 7 ),

		"Follow up on possible work",

		IF(

			AND( {Status} = "Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') > 3 ),

			"Follow up on job discussed",

			IF(

				AND( {Status} = "Not Interested", DATETIME_DIFF(TODAY(),{Last Contacted},'days') <121 ),

				“On hold for 3 months“,

				IF(

					AND( {Status} = "No Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') <46 ),

					“Nothing available”,

					IF(

						AND( {Status} = "Possible Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') <8 ),

						“Waiting to follow up“,

						IF(

							AND( {Status} = "Work Available", DATETIME_DIFF(TODAY(),{Last Contacted},'days') <4 ),

							“Waiting for response“,

							"Standing by..."

						)

					)

				)

			)

		)

	)

)

    )

    Is it because I’m using two equations for the same Status option? Figured it’d just run through these in order and if it found any to be TRUE it’d display the corresponding text. Mainly looking to do that so I can make a useful block with a simple email and follow-up alert system.

    Terms & ConditionsAccessibility statement