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.
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
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
Thank you Ken for this secure forum.
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
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
@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
Thank you Ken for this secure forum.
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
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
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
Bookmarks