# Nesting if then with and and or

#### vb4meee

##### New Member
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

##### New Member
My boss wants them BLACKED not blocked

#### Eric W

##### MrExcel MVP
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:

ABCDEFGHIJKLMNOP
1ProjectStartlengthFinishQuarter end dates3/31/20196/30/20199/30/201912/31/20193/31/20206/30/20209/30/202012/31/20203/31/20216/30/20219/30/2021
2A5/11/201911667/20/2022
3B7/2/2019759/15/2019
4C2/18/20207302/17/2022

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
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.

Last edited:

#### vb4meee

##### New Member
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

##### MrExcel MVP
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.