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
1	Project	Start	length	Finish	Quarter end dates	3/31/2019	6/30/2019	9/30/2019	12/31/2019	3/31/2020	6/30/2020	9/30/2020	12/31/2020	3/31/2021	6/30/2021	9/30/2021
2	A	5/11/2019	1166	7/20/2022
3	B	7/2/2019	75	9/15/2019
4	C	2/18/2020	730	2/17/2022

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =B2+C2 <tbody> </tbody>

<tbody>
</tbody>

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?

Duration	18Q3	18Q4	19Q1	19Q2	19Q3	19Q4
Min Days	Max Days	Start Date		MAX End Date	9/30	12/31	3/31	6/30	9/30	12/31
14	28	10/1/19	2019	10/28/19	TRUE					X
14	28	10/28/19	2019	11/24/19						X
14	28	11/24/19	2019	12/21/19						X
14	28	12/21/19	2019	1/17/20						X

<colgroup><col span="2"><col><col><col><col span="2"><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[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.