Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Generate collour coding in gant schedule in excel

  1. #1

    Generate collour coding in gant schedule in excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    875
    Articles
    0
    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

  3. #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
    Attached Files Attached Files

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    731
    Articles
    0
    Quote Originally Posted by magnus.er1 View Post
    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";""))
    Attached Files Attached Files
    Last edited by navic; 2017-01-11 at 04:53 PM.
    My personal Web Excel for beginners
    Attention! In the formulas as a separator I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    875
    Articles
    0
    @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

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    731
    Articles
    0
    Quote Originally Posted by Pecoflyer View Post
    @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
    My personal Web Excel for beginners
    Attention! In the formulas as a separator I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

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

  8. #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

  9. #9
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    731
    Articles
    0
    Quote Originally Posted by magnus.er1 View Post
    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

    My personal Web Excel for beginners
    Attention! In the formulas as a separator I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  10. #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
    Click image for larger version. 

Name:	Capture 1.JPG 
Views:	8 
Size:	55.0 KB 
ID:	6243

    Br Magnus
    Attached Files Attached Files
    Last edited by magnus.er1; 2017-01-12 at 04:20 PM.

Page 1 of 2 1 2 LastLast

Posting Permissions

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