Excel help

2
Posted 2nd Mar
Hi all,

Need some excel help please.

Been asked to create a gaant chart on excel using some data.

I could use the following conditional formatting formula if my data was set out individual dates such as;
01/01/20 (e2), 02/01/20 (f2)etc.
And I have rows for Tasks with start date and end date.
=AND(e2 >=start date, e2<=end date)

But my data doesnt have individual dates it has 01/01/20 (b2), 08/01/20 (c2), 15/01/20 (d2), 22/01/20 (e2) etc. so need to add something like...

any idea what I could use please. Want cells highlighted if start to end dates match that on the calendar area...b2, c2, d2 etc.

Thanks
Community Updates
Ask

Groups

2 Comments
Lots of example n the web. not sure if i am reading the problem correctly. I created one i use for my project tracking its simple with NO automation (i.e. vba) but can be added if you wanted. Depending on how you structure (mine is set up as week beginning but with a check that the start date falls within the week monday to friday and end date is basically less than of equal to the due date) The formula I use is: =IF($F15="",0,IF(AND(OR(BW$7>=$F15,(BW$7+5)>=$F15),BW$7<=$G15),1,0)) and the gantt chart image is attached. the formula adds a 1 or 0 in the cell which i then color the same colour as the background using conditional formatting.

If no date is entered it defaults to 0. I also set the absolutes so that i can fill the same formula across without having to redo the formula. simply need to extend the date in row 7. If I get some time I could sanitise my spreadsheet if its what you are looking for or the above doesn't make sense.40030400-mbjhF.jpg40030400-aofHd.jpg

As I mentioned if you look on google you will also find lots and lots of examples. Unless i completely misunderstood your ask. if so apologise in advance.
@e-aussie - That's exactly what I was looking for!! many many thanks. Obviously tweeked the formula according to what I required and worked first time! super brilliant.

And thanks for taking time out...was trying various different ways for a few hours yday!

:-)
Post a comment
Avatar
@
    Text

    Discussions

    Discussions

    Top Merchants