Generate collour coding in gant schedule in excel

magnus.er1

New member
Joined
Jan 9, 2017
Messages
11
Reaction score
0
Points
0
Hello.

I have got help from you guys by solving the issue of marking a cell with a (X) in the cell range of start and end date , now i would like to add an additional column named "new end date" and in the gant schedule all cells should be marked with (Y) from the "end date" to the new end date.

I will then use conditional formating to color code dependent on if it´s a Y or a X. but thats the simple task (no need for help there :)

The the formula i am using is

=IF(AND(K$3>=$F5;K$3<=$G5);"X";"") to mark (X) in cells from start to en date

and i would like to add something like this to the formula but does not know how to combine these two

=IF($A2=B$1;"Y";"")


Please look at attached example for deeper understanding.

Thanks in advance Magnus
 

Attachments

  • Example.xlsx
    33.7 KB · Views: 8
It depends on what is the most important, the X or the Y
Assuming the X takes precedence =IF(AND(K$3>=$F5;K$3<=$G5);"X";IF($A2=$B1;"Y";""))

I changed B$1 to $B1 but I might be wrong.
If the Y is more important, just exchange the conditions
 
Hello,
Thanks for the qiick reply, apriciated!

Unfourtinatly i have no lyck with that code,
It is functional for the (X) on start and end date, but when looking in the new date cell i get"name" error in the cel lwhere (Y) should be present.

For the functionality it is not possible to chose whats more importand Y or X, the functionality builds on the thought that X is always present when start/end date is written. Y is only there when new end date is documented.

Formula in cell that should show (Y) IF(AND(O$3>=$F5;O$3<=$G5);"X";IF(O$3=$H5;"Y";""))

Formula in start cell =IF(AND(O$3>=$F5;O$3<=$G5);"X";IF(O$3=$H5;"Y";""))

Uploaded example sheet with new formula included.

Thanks in advance Magnus
 

Attachments

  • Example2.xlsx
    33.8 KB · Views: 8
Unfourtinatly i have no lyck with that code,
@pecoflyer is missed TRUE argument
Try in K5 cell (copy across)
Code:
=IF(AND(K$3>=$F5;K$3<=$G5)=TRUE;"X";IF(K$3=$H5;"Y";""))
 

Attachments

  • magnus.er1-navic7206.xlsx
    34.9 KB · Views: 7
Last edited:
@navic
The TRUE argument is implicit, so it is unnecessary and therefore not missing in my formula, and, if it were, it would not return a NAME error

@Magnus
A NAME error means a function is spelled incorrectly. For some reason in the sheet you posted, if your retype the second IF the formula provided works as expected. Why this happened, I have no idea
 
@navic
The TRUE argument is implicit, so it is unnecessary
You're right, and I had a mistake in the beginning and I did not understand why?
After my modifications everything worked as it should.
Strangely, your original formula now works ok?

But it does not matter, it is important that now works :)
 
Hello, Thanks everyone for your help!
@pecoflyer, i found the error ,i am having a swedish Excel, and i had missed to change the last "IF" command to "OM" thats "if" in swedish =)

So now it is fully functional.

Thanks again your are the best!
//Best regards Magnus
 
Hello, after some testing i can see that there is an issue with this formule, maby thats what you tried to explain in the beginning.

Example scenario
If i use this formula =IF(AND(K$3>=$F5;K$3<=$G5);"X";IF(K$3<=$H5;"Y";""))

Start date 2017-01-18
End date 2017-01-20
New End date 2017-01-24

With this formula all cells within range of start /end date will be marked with X and all cells within range of end date and new end date will be marked with Y. But also the dates before startdate will be marked with a Y.

How do i get around this issue?

//Br Magnus
 
With this formula all cells within range of start /end date will be marked with X and all cells within range of end date and new end date will be marked with Y. But also the dates before startdate will be marked with a Y.
You are unclear. See screenshot below

 
Hello, se below picure. thats my issue, cells that has no planned date gets an Y , i have also updated the attached example xls sheet with the problem.

The dates 16-17 Jan gets marked with an Y but they are not scheduled
Capture 1.JPG

Br Magnus
 

Attachments

  • magnus.er1-navic7206 0112.xlsx
    35.8 KB · Views: 9
Last edited:
cells that has no planned date gets an Y , i have also updated the attached example xls sheet with the problem.
In your example sheet, formula in K5 cell is not correct

=IF(AND(K$3>=$F5;K$3<=$G5);"X";IF(K$3<=$H5;"Y";""))

You need formula as below

=IF(AND(K$3>=$F5;K$3<=$G5);"X";IF(K$3=$H5;"Y";""))

btw: please see screenshot in my post #9
 
Hello,
Thanks for your quick reply,
Yes the code that you provided me with is working :) but it is missing an important function, in my previously attached xlx document you find my modified formula that fills the gap between end date and new endnote with (Y).
As i tried to explain, when using my modified formula the gaps will be filled in, but it will also fill with y in the cells before the "start date"

Thanks in advance Magnus
 
but it is missing an important function,

In my Excel 2013

magnus_er1_2.png

My apologies, but I can not help you more. Or I do not understand you well.
English is not my native language.

BTW:
Let's try a different way.
Please attach an Excel file with the expected results. (Forget your formula)

PS. Do you perhaps want to "Y" in cells O5 and P5 on the screenshot?
 
Last edited:
PS. Do you perhaps want to "Y" in cells O5 and P5 on the screenshot?
If answer "Yes" then in Cell K5 try formula below (copy across)
Code:
=IF(AND(K$3>=$F5;K$3<=$G5);"X";IF(AND(K$3>=$G5;K$3<=$H5);"Y";""))

magnus_er1_3.png
 
Hello,
Thanks Navic, now everything works,
Thanks for a good support
Br Magnus
 
You are welcome
 
Back
Top