Choosing a date based on multiple criteria

omf_24

New Member
Joined
Oct 10, 2013
Messages
8
Please help. I have a column called "Code" and three date headings as shown below.

If the code is 03, 04, or 05, I want the result to be the later date of Date 3 or Date 2, otherwise I would want the earlier date to appear. If both are blank then Date 1 should be used.


Code
Date 1Date 2
Date 3
Result
A16/11/2013
2/5/20143/15/20133/15/2013
056/25/20131/17/20145/21/20145/21/2014
0411/13/201211/13/2012
0712/18/20121/17/20131/17/2013

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
untested:
=if(and(c2="",d2=""),b2,if(or(a2="03",a2="04",a2="05"),max(d2,c2),min(d2,c2)))
 

ProfessorPorcupine

Board Regular
Joined
Sep 20, 2014
Messages
72
Try this, it is pretty long but I think it works ok: =IF(AND(C3=0,D3=0),B3,IF(OR(A3="03",A3="04",A3="05"),IF(D3>C3,D3,C3),IF(AND(C3<B3,C3<>0),C3,IF(AND(D3<C3,D3<>0),D3,C3))))
 

omf_24

New Member
Joined
Oct 10, 2013
Messages
8
untested:
=if(and(c2="",d2=""),b2,if(or(a2="03",a2="04",a2="05"),max(d2,c2),min(d2,c2)))

Thanks, this is working great if the code is 03, 04, or 05, but not if it's anything other than 03, 04, or 05. In that case it should be returning the earlier date rather than the later date.
 

omf_24

New Member
Joined
Oct 10, 2013
Messages
8

ADVERTISEMENT

Try this, it is pretty long but I think it works ok: =IF(AND(C3=0,D3=0),B3,IF(OR(A3="03",A3="04",A3="05"),IF(D3>C3,D3,C3),IF(AND(C3<b3,c3<>0),C3,IF(AND(D3<c3,d3<>0),D3,C3))))

Sorry, but why cell's C30, and D30? </c3,d3<></b3,c3<>
 

omf_24

New Member
Joined
Oct 10, 2013
Messages
8
Thanks, this is working great if the code is 03, 04, or 05, but not if it's anything other than 03, 04, or 05. In that case it should be returning the earlier date rather than the later date.

Sorry, this is working correctly, thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,453
Messages
5,528,871
Members
409,842
Latest member
mfernandezcean
Top