Nested IF/And/OR function with multiple conditions

[Cazbanana]

Hi,

First time here. I have a query with dates and if functions. Not sure if this is possible but need some guidance to see if this is actually possible with so many conditions;

[TABLE="width: 163"]
<tbody>[TR]
[TD="class: xl63, width: 163"]If the date in G is sooner than J than this field should be the difference of the 2 dates in days * D (D is a number)

If the date in G is later than J this field should be blank

If the date in G = J than this field should be 5 * D

If there is no date in J than take the date from N and follow these rules

If the date in G is sooner than N than this field should be the difference of the 2 dates in days * D

If the date in G is later than N this field should be blank

If the date in G = N than this field should be 5 * D

Thanks in advance!

Caz. [/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

as explained above if G<J then difference x D
G<N then difference x D

you cannot have both as they are different

 

[AlanY]

check if formula in Col K met your requirement

Book1
	D	E	F	G	H	I	J	K
1	10			01/11/2018			29/10/2018
2	10			01/11/2018			01/11/2018	50
3	10			01/11/2018			07/11/2018	60
4	10			01/11/2018			29/10/2018
5	10			01/11/2018			04/11/2018	30
6	10			01/11/2018			02/11/2018	10
7	10			01/11/2018			06/11/2018	50
8	10			01/11/2018			08/11/2018	70
9	10			01/11/2018			30/10/2018
10	10			01/11/2018			25/10/2018
Sheet2

Cell Formulas
Range	Formula
K1	=IF(G1="",IF(N1J1-N1)*D1,IF(N1>J1,"",5*D1)),IF(G1J1-G1)*D1,IF(G1>J1,"",5*D1)))

 

[Cazbanana]

Hi Andy,

Thank you so much for your speedy reply!!

Jiggled some of the cell references around, but it worked a treat!

Caz x

 

[AlanY]

you're welcome.
btw, it's Alan

 

[oldbrewer]

typos

as explained above if G<j then="" difference="" x="" d
< J then (j-g)*d
<n then="" difference="" x="" d
if g < n then (n-g)*d
you cannot have both as they are different - you have to prioritise them</n></j>

 

[Cazbanana]

Apologies Alan!

 

[Cazbanana]

Thank you for clarifying.