Resource Gantt Chart with dates help needed please

Hi
I can't tell from your spreadsheet how you would like the finished product to look. A different colour for each person and each start date is going to get very colourful very quickly as very few tasks are started on the same day by the same person. You could aggregate eg all tasks started in a particular month but with 12 months and 30+ people that still gives you several hundred unique colours, which is way beyond what conditional formatting could manage.

First of all, you need to get dates across your chart, so use these formulae in the cells stated:

G8: =G$7
H8: =G$7+1
I8: =G$7+2
J8: =G$7+3
K8: =G$7+4

Note that I have just removed the DAY( ) function from those cells. Format the cells using a Custom Format of d, and then you can copy that block of cells across on row 8 for each week. The layout will look exactly the same as the way you had it.

Then you can use this formula in cell G11:

=IF(AND($C11>=G$8,$D11<=G$8),$F11,"")

which will return the initials, as appropriate. The formula can be copied across and down, as required.

You can then apply your CF to those cells, although I agree with NickyC that this will become very colourful.

Hope this helps.

Pete

Thanks Pete.

I am still struggling as the formula does not take into account the date duration i.e. if BS is start date 3/7/19 and finish date 06/07/2019 i would like the gantt chart to colour the condition formatted colour for BS from 3/7/19 till 6/7/2019

how to fix this issue please xx

Sorry, you should use this formula in G11:

=IF(AND(G$8>=$C11,G$8<=$D11),$F11,"")

(having made the other changes on row 8), and then you can copy this across and down as required.

Hope this helps.

Pete

To show this in action, I've copied the formula to the cells G11:AE16 in the attached file, and I've also modified the dates in cells D11 and D14 (coloured yellow) so you can see the effect.

You already have several CF conditions applied, though I'm not sure how these relate to the initials - I've not changed them.

Hope this helps.

Pete

Thanks Pete, thats Brill x

Glad it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hi again Pete,

Hope you are well and keeping safe.

Hope you don't mind me asking this question here, really need some help on this please...

***I am trying to use a criteria to return a text value and condition format the cell with the required colour based on the Text.
*** Cell C9 is where I have tried the formula and the lookup cell is B9

GREEN = "On track" >=1.0
YELLOW = "Behind schedule or budget" >0.85
RED = "Needs immediate attention" >0.65