Nesting if then with and and or

vb4meee

New Member
Joined
Dec 20, 2018
Messages
3
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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
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
Joined
Dec 20, 2018
Messages
3
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?


Duration18Q318Q419Q119Q219Q319Q4
Min DaysMax DaysStart Date MAX End Date9/3012/313/316/309/3012/31
142810/1/19201910/28/19TRUE X
142810/28/19201911/24/19 X
142811/24/19201912/21/19 X
142812/21/1920191/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
Joined
Aug 18, 2015
Messages
8,787
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.
 

Forum statistics

Threads
1,077,822
Messages
5,336,566
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top