If and or function

[melq64]

Appreciate any help in advance

Having trouble coming up with a formula for column J (Time at Depot) to return number of days (rounded up) it takes for an item to get delivered based on the following scenario below:

using cell J2 as reference

IF I2="IMC", return C2

IF I2="AMC", return E2

IF I2="WNM" & A2="C4", H2-B2 OR IF I2="WNM" & A2="C6", H2-D2 [if B2 or D2 blank, return "no scan"]

IF I2="WKM", H2-D2

IF I2="AKL delivery", H2-D2

IF I2="CHC delivery", H2-F2 [if F2 blank, return "no scan"]

IF I2="TRG", H2-D2

IF I2="CHM", return G2 [if G2 blank, return "no scan"]

	A	B	C	D	E	F	G	H	I	J
1	Product	Exit IMC	Time at IMC	Exit AMC	Time at AMC	Exit CHM	Time at CHM	Delivered Date	Delay Point	Time at Depot
2	C6	10-08-2017 11:22:00 AM	21hrs	10-08-2017 8:35:00 PM	6hrs	12-08-2017 5:50:00 AM		12-08-2017	IMC
3	C6	06-08-2017 10:29:00 AM	2days	08-08-2017 4:23:00 AM	2days			10-08-2017	AMC
4	C6	09-08-2017 4:46:00 PM	4hrs	09-08-2017 8:30:00 PM	2hrs			11-08-2017	WNM
5	C4	10-08-2017 3:02:00 PM	23hrs					12-08-2017	WNM
6	C6	13-08-2017 1:06:00 PM	2days	14-08-2017 10:02:00 PM	2days			16-08-2017	WKM
7	C6	06-08-2017 1:06:00 PM	3hrs	08-08-2017 4:08:00 AM	2days			09-08-2017	AKL delivery
8	C4	07-08-2017 11:39:00 AM	4hrs			08-08-2017 5:50:00 AM	7hrs	09-08-2017	CHC delivery
9	C6	08-08-2017 10:54:00 AM	22hrs	08-08-2017 9:45:00 PM	8hrs			10-08-2017	TRG
10	C6	13-08-2017 11:03:00 AM	2days	14-08-2017 11:48:00 PM	2days	16-08-2017 6:02:00 AM	21hrs	17-08-2017	CHM

<tbody>
</tbody>

 

[Special-K99]

Try (untested)

=IF(I2="IMC",C2,IF(I2="AMC",E2,IF(I2="WNM",IF(A2="C4",IF(B2="","No Scan",H2-B2),IF(A2="C6",IF(D2="","No Scan",H2-D2),IF(I2="WKM",H2-D2,IF(I2="AKL delivery",H2-D2,IF(I2="CHC delivery",IF(F2="","No Scan",H2-F2),IF(I2="TRG",H2-D2,IF(I2="CHM",IF(G2="","No Scan",G2)))))))))))

 

[oldbrewer]

=IF( I2="IMC", C2, IF( I2="AMC", E2,IF(and( I2="WNM" ,A2="C4"), H2-B2 , IF(and( I2="WNM" , A2="C6"), H2-D2 ,if(or( B2="", D2=""), "no scan",IF (I2="WKM", H2-D2, IF( I2="AKL delivery", H2-D2, IF (I2="CHC delivery", H2-F2 ,if( F2 ="", "no scan",IF (I2="TRG", H2-D2,IF (I2="CHM", if( G2 ="","no scan",G2))))))))))))

this is the approach but need more info on if F2 is blank because F2 is called before that, same with G2

 

[melq64]

Thanks Special-K99 but I'm getting a FALSE value.

However, am getting correct values for the part of the function where it says return associated value of corresponding cell e.g. IF(I2="IMC",C2)

Any suggestions on how to amend the FALSE values?

 

[melq64]

Thanks oldbrewer but am getting error message with function "There's a problem with this formula"

What further info are you after regarding F2 & G2?

 

[oldbrewer]

you use them in your formula before saying if they are blank then return no scan

do you want F2 and G2 checked if blank at the beginning - IF (I2="CHC delivery", H2-F2 ,if( F2 ="", "no scan",IF.....makes no sense

the formula needs checking for equal numbers of brackets etc - I was giving you a steer

 

[oldbrewer]

IF I2="IMC", return C2

IF I2="AMC", return E2

IF I2="WNM" & A2="C4", H2-B2 OR IF I2="WNM" & A2="C6", H2-D2 [if B2 or D2 blank, return "no scan"]

IF I2="WKM", H2-D2

IF I2="AKL delivery", H2-D2

IF I2="CHC delivery", H2-F2 [if F2 blank, return "no scan"]

IF I2="TRG", H2-D2

IF I2="CHM", return G2 [if G2 blank, return "no scan"]

is my interpretation below correct ?

imc

c2

amc

e2

wnm and a2="C4"

h2-b2

but if b2= blank "no scan"

wnm and a2="C6"

h2-d2

but if d2=blank "no scan

chc delivery

f2-f2

but if f2 = blank "no scan"

chm

g2

but if g2 = blank "no scan"

otherwise

wkm

akl delivery

h2-d2

DO YOU WANT D2 CHECKED FOR BLANKNESS HERE AS WELL

trg

<colgroup><col span="14"></colgroup><tbody>
</tbody>

 

[melq64]

Apologies now I understand.

Yes that's correct except for chc delivery f2-f2, should be h2-f2

And yes check D2 for blank

Sorry just realized that E2 & C2 need to be checked for blanks as well i.e. if blank return "no scan"

 

[melq64]

any suggestions on the final formula?

 

[melq64]

any suggestions on the final formula oldbrewer / Special-K99?

appreciate your help