Nesting if then with and and or

[vb4meee]

I have a sheet. There is a date column for length of task, start and finish of project tasks. The finish column is a formula of length of task plus start (so not in date format). To the right of these dates are quarters with quarter end dates. If the project is being worked on during this quarter, I am to block out the cell with black using conditional formatting. Problem is, when the start date is, say 5/11/19 and end date is 7/20/22, then my boss wants all those quarters to be blocked. I was trying to nest ifs with ands and ors. Anyone know how OR if there is a simpler way?

 

[vb4meee]

My boss wants them BLACKED not blocked

 

[Eric W]

Welcome to the MrExcel forum.

I don't see exactly how your sheet is laid out, but I think it must be something like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Project	Start	length	Finish	Quarter end dates
A
B
C

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]3/31/2019[/TD]
[TD="align: right"]6/30/2019[/TD]
[TD="align: right"]9/30/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]3/31/2020[/TD]
[TD="align: right"]6/30/2020[/TD]
[TD="align: right"]9/30/2020[/TD]
[TD="align: right"]12/31/2020[/TD]
[TD="align: right"]3/31/2021[/TD]
[TD="align: right"]6/30/2021[/TD]
[TD="align: right"]9/30/2021[/TD]

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

[TD="align: right"]5/11/2019[/TD]
[TD="align: right"]1166[/TD]
[TD="align: right"]7/20/2022[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/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"]7/2/2019[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]9/15/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]2/18/2020[/TD]
[TD="align: right"]730[/TD]
[TD="align: right"]2/17/2022[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=B2+C2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

If that's the case, then select columns F:Z (or however far over your dates go). Click Conditional Formatting > New Rule > Use a Formula > and enter:

=AND(MIN(F$1,$D1)-MAX(EOMONTH(F$1,-3)+1,$B1)>0,$A1<>"",ROW()>1)

Click Format... and select a black fill color.

The first part of the formula (red) is a formula that counts how many days overlap between 2 date ranges. The 2 ranges are B2 and D2, and F1-90 and F1. If the number is >0 then we know that at least part of the project occurred during that quarter. The part in green keeps the formatting from occurring on a blank line, and the part in blue keeps it from highlighting the header line. The absolute symbol ($) is carefully chosen so that it works over the whole range.

If your sheet doesn't look exactly like that and you can't adapt the formula, let me know how it looks and I'll see what I can do.

 

[vb4meee]

There is no end date in column D. That column is a formula. It is "Start Date" plus max days minus 1. Can I use that in this same formula? A formula inside the formula?


[TABLE="width: 564"]
<colgroup><col span="2"><col><col><col><col span="2"><col><col span="2"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 5"]Duration[/TD]
[TD]18Q3[/TD]
[TD]18Q4[/TD]
[TD]19Q1[/TD]
[TD]19Q2[/TD]
[TD]19Q3[/TD]
[TD]19Q4[/TD]
[/TR]
[TR]
[TD]Min Days[/TD]
[TD]Max Days[/TD]
[TD]Start Date[/TD]
[TD] [/TD]
[TD]MAX End Date[/TD]
[TD]9/30[/TD]
[TD]12/31[/TD]
[TD]3/31[/TD]
[TD]6/30[/TD]
[TD]9/30[/TD]
[TD]12/31[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]28[/TD]
[TD]10/1/19[/TD]
[TD]2019[/TD]
[TD]10/28/19[/TD]
[TD]TRUE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]28[/TD]
[TD]10/28/19[/TD]
[TD]2019[/TD]
[TD]11/24/19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]28[/TD]
[TD]11/24/19[/TD]
[TD]2019[/TD]
[TD]12/21/19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]28[/TD]
[TD]12/21/19[/TD]
[TD]2019[/TD]
[TD]1/17/20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X


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

 

[Eric W]

Sure, if you look you'll see that in my example I had a formula in the D column. You'll have to make a few tweaks to the formula since your ranges are in different locations, but it should work fine. I'd think it would be:

=AND(MIN(F$2,$E1)-MAX(EOMONTH(F$2,-3)+1,$C1)>0,$A1<>"",ROW()>2)

If your start/end dates are in C and E, and your quarter end dates are in row 2.