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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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,663
Messages
5,549,290
Members
410,908
Latest member
Allen P
Top