Multiple conditions to get one result

[bomexport_indus]

Hi

i’m trying to get one result from three colomns with date type but i can’t find the suitable expression to use.

DATE COLUMN ONE IS : [EXPEDITION DATE]
DATE COLUMN TWO : [SHIPPING ARRIVAL DATE]
DATE COLUMN THREE : [SHIPPING RECEPTION DATE]

the expression should works as bellow :

if [EXPEDITION DATE] + [SHIPPING ARRIVAL DATE] + [SHIPPING RECEPTION DATE] are all blank so result is "NOT SHIPPED"
if [EXPEDITION DATE] isnotblank + ([SHIPPING ARRIVAL DATE] + [SHIPPING RECEPTION DATE]) are blank so result is "SHIPPED"
if ([EXPEDITION DATE] + [SHIPPING ARRIVAL DATE] are not blank) + [SHIPPING RECEPTION DATE] is blank so result is "ARRIVED"
if [EXPEDITION DATE] + [SHIPPING ARRIVAL DATE] + [SHIPPING RECEPTION DATE] are all notblank so result is "RECEIVED"

WITH MANY THANKS

[hru]

Hi @bomexport_industry, Welcome to Community.

I am sure that there are more efficient expression other than my suggestion below, but while waiting for other to join the discussion, you might want to try:

    IFS(
		ISBLANK([EXPEDITION DATE]),
		"NOT SHIPPED",
		ISNOTBLANK([EXPEDITION DATE]),
		"SHIPPED",
		ISBLANK([SHIPPING RECEPTION DATE])
		"ARRIVED",
		ISNOTBLANK([SHIPPING RECEPTION DATE]),
		"RECEIVED",
	)

Update:

   	IFS(
		ISBLANK([EXPEDITION DATE]),
		"NOT SHIPPED",
		ISBLANK([SHIPPING ARRIVAL DATE]),
		"SHIPPED",
		ISNOTBLANK([SHIPPING ARRIVAL DATE])
		"ARRIVED",
		ISNOTBLANK([SHIPPING RECEPTION DATE]),
		"RECEIVED",
	)

[bomexport_indus]

Hi
thanks for your answer

No this expression is not suitable for me, because result depends on the entire conditions i’ve mentionned.

[Lynn · Accepted solution]

Hi @bomexport_industry maybe
IFS(
AND(
ISBLANK(
[EXPEDITION DATE]),
ISBLANK(
[SHIPPING ARRIVAL DATE]),
ISBLANK(
[SHIPPING RECEPTION DATE])),“NOT SHIPPED”,
AND(
ISNOTBLANK(
[EXPEDITION DATE]),
ISBLANK(
[SHIPPING ARRIVAL DATE]),
ISBLANK(
[SHIPPING RECEPTION DATE])),“SHIPPED”,
AND(
ISNOTBLANK(
[EXPEDITION DATE]),
ISNOTBLANK(
[SHIPPING ARRIVAL DATE]),
ISBLANK(
[SHIPPING RECEPTION DATE])),“ARRIVED”,
AND(
ISNOTBLANK(
[EXPEDITION DATE]),
ISNOTBLANK(
[SHIPPING ARRIVAL DATE]),
ISNOTBLANK(
[SHIPPING RECEPTION DATE])),“RECIEVED”
)

ooPS Think I got that twice. Have deleted half of it.

[bomexport_indus]

THANK YOU SO MUCH !
This wone it works pretty well

You saved my life

[Suvrutt_Gurjar]

Another approach may be to implement the functionality in two columns.

The first VC column called say [Combined Status] could be something like

NUMBER(CONTATENATE(IF(ISBLANK([EXPEDITION DATE]),0,1), IF( ISBLANK([SHIPPING ARRIVAL DATE]),0,1), IF( ISBLANK([SHIPPING RECEPTION DATE]),0,1)))

Then in the [Results] column, an expression could be

SWITCH([Combined Status], 0, “Not Shipped”, 100, “Shipped”, 110, “Arrived”, 111, “Received”, “Not applicable”)

[Steve]

Similar to @Heru’s suggestion:

IFS(
  ISNOTBLANK([SHIPPING RECEPTION DATE]),
    "RECEIVED",
  ISNOTBLANK([SHIPPING ARRIVAL DATE]),
    "ARRIVED",
  ISNOTBLANK([EXPEDITION DATE]),
    "SHIPPED",
  TRUE,
    "NOT SHIPPED"
)

[Suvrutt_Gurjar]

Compact one @Steve and @Heru.