Conditional formatting for project planning

[MrTomB]

Hi

I want a really basic sheet that i can fill in a project with its start and finish dates and see cells colour based on the dates enter

See attached, i want the yellow to happen automatically

Eventually i will have each team fill in a tab and then a master tab that consolidates it all

Thanks

 

[Joe4]

Welcome to the Board!

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[MrTomB]

Hi, not a great start, cannot add images and company wont allow those downloads, does this work?

2018

2019

2020

2021

Project

Start

Finish

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Project 101

01/01/2018

02/02/2019

Project 102

01/01/2018

02/02/2020

Project 103

01/01/2018

02/02/2021

<tbody>
</tbody>

 

[Joe4]

Yes, we can see that.
In what cell is the Project 101 for Q1 2018 located?
Is it cell D3? If not, what cell are you using?
I am asking because I am trying to incorporate the relative column into the formulas.

 

[MrTomB]

Hi Joe

The cell i think you are referring to is D3. So this would be the first highlight cell for project 101

Hope the formula isnt too complex so i can tweak it further down the line


Thanks Tom

 

[Joe4]

Hope the formula isnt too complex so i can tweak it further down the line

Its long, though not necessarily complex.

So, in your example, select cells D3:S5 (the range you want to apply the Conditional Formatting to), and then enter this Conditional Formatting formula:

=AND($B3<=DATE(2017+INT(COLUMN()/4),(MOD(COLUMN(),4)*3)+4,0),$C3>=DATE(2017+INT(COLUMN()/4),(MOD(COLUMN(),4)*3)+1,1))

and choose your yellow formatting option.

Note that the formula currently has the year piece hard-coded in there. We could use the years found in row 1, but I would recommend getting rid of the merged cells, and use the "Center Across Selection" formatting option instead. Merged cells cause numerous problems, and should really be avoided!
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[MrTomB]

Note that the formula currently has the year piece hard-coded in there. We could use the years found in row 1, but I would recommend getting rid of the merged cells, and use the "Center Across Selection" formatting option instead. Merged cells cause numerous problems, and should really be avoided!
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

Ok, lesson learnt on the very tempting merge and centre button! how would i remove the hard coding on the year 2017?

Thanks for the help to this point though!

 

[Joe4]

how would i remove the hard coding on the year 2017?

Assuming the first year is being placed in cell D1, and they always go in consecutive order, then use this:

=AND($B3<=DATE($D$1+INT(COLUMN()/4)-1,(MOD(COLUMN(),4)*3)+4,0),$C3>=DATE($D$1+INT(COLUMN()/4)-1,(MOD(COLUMN(),4)*3)+1,1))

 

[MrTomB]

Assuming the first year is being placed in cell D1, and they always go in consecutive order, then use this:

=AND($B3<=DATE($D$1+INT(COLUMN()/4)-1,(MOD(COLUMN(),4)*3)+4,0),$C3>=DATE($D$1+INT(COLUMN()/4)-1,(MOD(COLUMN(),4)*3)+1,1))

Wow, now whats the best way of understanding what this formula does so when i make changes i know how to edit? I have basics in excel functions

 

[Joe4]

Basically, here is what we are trying to do:
Check to see if BOTH of the following conditions are true:
- The "Start" date (column B) is before (or on) the last day of the quarter we are looking at
- The "Finish" date (column C) is after (or on) the first day of the quarter we are looking at

So, we need to build these quarter start/end dates. We do that by using the the DATE function, which has the format:
=DATE(year, month, day)

The key to getting this to working for every cell without having to adjust the formula is to make use of two important things:
- The absolute reference sign ($). By placing this in front of column or row references, it "locks" down that part of the range reference, so it won't float when we move to other cells (see here for a good explanation of that: http://www.cpearson.com/excel/relative.aspx)
- The COLUMN() function, which returns the column number of whatever cell the formula is placed in. To see this, place this formula in cell A1 and copy across the row and see the value change.

The other two keys are the INT and MOD functions, which we use with division.
The INT function returns just the integer portion of a number (meaning, it drops any decimals)
The MOD function returns the remainder when dividing one number by another.
(You can Google these functions to see more details and examples of how they work, if you like).

So, now just piece it all together. If you want to see what the year portion returns, simply extract that part of the formula and place is in any blank row in column D, i.e.

=$D$1+INT(COLUMN()/4)-1

If you substitute each part, you will have:
=2018+INT(4/4)-1 = 2018+INT(1)-1 = 2018+1-1 = 2018
Because we are dividing by 4, the INT portion will jump up by one every 4th column (you can drag this formula out to column S to see how it changes).

You can repeat the same type of logic with the month (I will leave that part to you).
One other trick that I enlist, since months have different number of days, to get the last day of a particular month, you can choose day 0 of the following month, i.e.
To represent 3/31/2018, you could use:
=DATE(2018,3,31)
or
=DATE(2018,4,0)
The second is easier to work with, as you do not need to worry about with months have 30 days and which have 31.