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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

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,114,323
Messages
5,547,240
Members
410,779
Latest member
che55ysia
Top