Hi all
I got a lot of help from reading Chandoo's post with my code, but now I have come to a stop, I have a small problem, I have tried everything, I only have this last thing to do.
as you can see from the file, my date highlights are also on the next month (Dec)
I need them to only go to the current date that in the Starts & finish dates (Nov)
my code
Activity Name (cell A) is pick from a list, works ok
Duration (Days) (cell B) is work out by this =IF(C5=D5,1,DAYS360(C5,D5)+1) works ok
cells with no dates shows a 0 so I use this conditional formatting =ISBLANK(C5) & =ISBLANK(D5) this colors the 0 white
I also have conditional formatting to make the cell B red if the dates are done wrong =C5>D5
now the conditional formatting to make the blue color in the days cells for Nov =AND(E$4>=DAY($C5), E$4<=DAY($D5))
this work great, but it also shows the blue in the other months, I only want it to show for the month shown in the cel C & D
I tried this =IF( ABS(MONTH(C5) = MONTH(E2)),AND(E$4>=DAY($C5), E$4<=DAY($D5)),"") this works but I will have to add it to every cel needed, like 366 for a year, I realty need a conditional formatting to do this, I did try this
conditional formatting =AND(E$4>=DAY($C5), E$4<=DAY($D5))
conditional formatting =ABS(MONTH(C5) = MONTH(E2)) (tick stop if true)
this does not work, can anyone help please
https://www.dropbox.com/s/uahyt5k12nraa2b/Activity.xlsm
hope I used Dropbox ok
I got a lot of help from reading Chandoo's post with my code, but now I have come to a stop, I have a small problem, I have tried everything, I only have this last thing to do.
as you can see from the file, my date highlights are also on the next month (Dec)
I need them to only go to the current date that in the Starts & finish dates (Nov)
my code
Activity Name (cell A) is pick from a list, works ok
Duration (Days) (cell B) is work out by this =IF(C5=D5,1,DAYS360(C5,D5)+1) works ok
cells with no dates shows a 0 so I use this conditional formatting =ISBLANK(C5) & =ISBLANK(D5) this colors the 0 white
I also have conditional formatting to make the cell B red if the dates are done wrong =C5>D5
now the conditional formatting to make the blue color in the days cells for Nov =AND(E$4>=DAY($C5), E$4<=DAY($D5))
this work great, but it also shows the blue in the other months, I only want it to show for the month shown in the cel C & D
I tried this =IF( ABS(MONTH(C5) = MONTH(E2)),AND(E$4>=DAY($C5), E$4<=DAY($D5)),"") this works but I will have to add it to every cel needed, like 366 for a year, I realty need a conditional formatting to do this, I did try this
conditional formatting =AND(E$4>=DAY($C5), E$4<=DAY($D5))
conditional formatting =ABS(MONTH(C5) = MONTH(E2)) (tick stop if true)
this does not work, can anyone help please
https://www.dropbox.com/s/uahyt5k12nraa2b/Activity.xlsm
hope I used Dropbox ok
