Complex combination of IF/AND Formula (Help Needed)

[dmxcasper2]

Problem Statement:
I am having difficulty creating a formula that will take multiple AND and IF criteria into calculation based on today's date. Please see below for (4) logic statements that I have been trying to incorporate into a single formula string.

Column E is the output. (Where the formula would reside)

Formula is needed based on the following logic statements:

1. If there is no date in Column A, then default to "blank" in Column E
2. If there is a non-date attribute (example: "TBD", or "TBA") in Column A, then default to "blank" in column E
3. If there is a date in Column B, then subtract it from MAX of Column C and D.
4. If there is a date in Column A and Column B is blank, take the MAX date of Column C and D and subtract it from today's date

Any help is appreciate it.
I can't find a good source online to explain/teach the logic behind creating these AND and IF combinations.

 

[Fluff]

How about
=IF(OR(A2="",ISTEXT(A2)),"blank",IF(B2<>"",MAX(C2:D2)-B2,TODAY()-MAX(C2:D2)))

 

[jtakw]

Hi,

Another way to write that formula:

Book1
	A	B	C	D	E
2
3	TBD
4	9/29/2018	10/3/2018	10/2/2018	10/4/2018	1
5	9/29/2018		10/2/2018	10/30/2018	45
Sheet398

Cell Formulas
Range	Formula
E2	=IF(N(A2)=0,"",IF(B2,MAX(C2,D2)-B2,TODAY()-MAX(C2,D2)))

Formula in E2 copied down.

 

[dmxcasper2]

Thanx Fluff and jtakw. Both formulas work as intended.

Is is possible to also integrate the following logic statements to both of your existing formulas?

If Column A has a date and Column B is blank, and also if Column C or D is blank then equal "blank"
If Column A & Column B has a date, and also if Column C or D is blank then equal "blank"

 

[jtakw]

See if this does what you describe:

Book1
	A	B	C	D	E
2
3	TBD
4	9/28/2018	10/2/2018
5	9/29/2018	10/3/2018	10/2/2018	10/4/2018	1
6	9/28/2018		10/3/2018
7	9/29/2018		10/2/2018	10/30/2018	45
Sheet398

Cell Formulas
Range	Formula
E2	=IF(OR(N(A2)=0,C2="",D2=""),"",IF(B2,MAX(C2,D2)-B2,TODAY()-MAX(C2,D2)))

 

[dmxcasper2]

See if this does what you describe:

	A	B	C	D	E
TBD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]9/28/2018[/TD]
[TD="align: right"]10/2/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]9/29/2018[/TD]
[TD="align: right"]10/3/2018[/TD]
[TD="align: right"]10/2/2018[/TD]
[TD="align: right"]10/4/2018[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]9/28/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/3/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]9/29/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/2/2018[/TD]
[TD="align: right"]10/30/2018[/TD]
[TD="align: right"]45[/TD]

</tbody>

Sheet398​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(OR(N(A2)=0,C2="",D2=""),"",IF(B2,MAX(C2,D2)-B2,TODAY()-MAX(C2,D2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

jtakw,

I used the above formula and dissecting the data, I found couple of calculation issues based on the below logic satements

1. If there is no date in Column A, then default to "blank" in Column E
2. If there is a non-date attribute (example: "TBD", or "TBA") in Column A, then default to "blank" in column E
3. If there is a date in Column B, then subtract it from MAX of Column C and D.
4. If there is a date in Column A and Column B is blank, take the MAX date of Column C and D and subtract it from today's date
5. If Column A has a date and Column B is blank, and also if Column C or D is blank then equal "blank"
6. If Column A & Column B has a date, and also if Column C or D is blank then equal "blank"

 

[jtakw]

Thanx Fluff and jtakw. Both formulas work as intended.

Is is possible to also integrate the following logic statements to both of your existing formulas?

If Column A has a date and Column B is blank, and also if Column C or D is blank then equal "blank"
If Column A & Column B has a date, and also if Column C or D is blank then equal "blank"

In Both your Post # 4 and Post # 6 above, your written description says Either C OR D is blank, then return Blank, but your sample in Post # 6 shows when Both are blank, then return Blank.

Try this version:

Book1
	A	B	C	D	E
7	10/2/2018	10/10/2018	10/8/2018		2
8	10/2/2018	10/16/2018	10/8/2018		8
9	10/4/2018	10/17/2018	10/8/2018		9
10	10/16/2018	10/25/2018	10/19/2018	10/26/2018	-1
11	10/21/2018	11/1/2018	10/26/2018	10/30/2018	2
12	11/1/2018	11/2/2018	11/15/2018		-13
Sheet398

Cell Formulas
Range	Formula
E7	=IF(OR(N(A7)=0,AND(C7="",D7="")),"",IF(B7,B7-MAX(C7,D7),MAX(C7,D7)-TODAY()))

BTW: it would be helpful if you can upload a table rather than a picture, so that helpers can copy and paste your data for testing.