Thread: Generate collour coding in gant schedule in excel

1. Generate collour coding in gant schedule in excel

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  Reply With Quote

2. 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  Reply With Quote

3. 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  Reply With Quote

4. Originally Posted by magnus.er1 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";""))  Reply With Quote

5. @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  Reply With Quote

6. Originally Posted by Pecoflyer @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   Reply With Quote

7. 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  Reply With Quote

8. 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  Reply With Quote

9. Originally Posted by magnus.er1 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   Reply With Quote

10. 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 Br Magnus  Reply With Quote Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•