Show progress on a 'gantt' style spreadsheet

[halesowenmum]

Hi. I've got a worksheet with dates populated across multiple columns between col P thru col IN.

In E14 is the start date, in F14 is the due date and in I14 is the '% complete' - manually entered based on how far through with that task the person thinks they actually are.

My s/sheet already returns a shaded colour to the cell to represent the date range that exists between the start and due dates - great so we know the total duration available.

There's two more things I'd like to cover off but I don't know if it's possible:
1. Let's say you've got the start of 1 month to the end of the next but one month, if that date today was in the middle of those start and end dates, I'd like to see shading that shows where we would expect to be
2. Based on the % complete the user enters I'd like to see different shading for this so if in fact we are at the halway point for this task, but the user reports that they've only done 10% of it, I'd like to see just the first 10% of relevant dates shaded in.

So you'd have something that goes right to left along the columns Gantt-stylee that has shading/fill for the total time available, shading/fill for where it should be, and shading/fill for where it actually is, all on the same 'row' (or bar chart bar(s), or whatever is the solution - if there is one).

Presumably there'd be a need to accommodate where a task was actually ahead of schedule (a girl can dream...) - not only that a task might be ahead of schedule but that there's a way of showing it as per description!

Anyone any ideas?

 

[Peter_SSs]

See if this does what you want. Note that my dates are in d/m/y format. Dates in P1:AV1 are from 31 July 2019 to 1 September 2019 but I have formatted them to show just d/m so my screen shot isn't too wide.

Blue represents the user's estimated % completed
Amber shows where we would expect to be (that is, up to today's date - 4 August for me when posting this)
Green shows remaining task time

You need to ensure that the CF rules end up in the same order shown below.

Note from rows 15 & 16 that if the estimated completion % (col I) is >= where the task is expected to be by today's date then the amber disappears and you know the task is on or ahead of schedule. (If it is like most tasks that I am involved with that circumstance will rarely occur. )

<div class="cms_table">

Excel Workbook
	E	F	G	H	I	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1						31/7	1/8	2/8	3/8	4/8	5/8	6/8	7/8	8/8	9/8	10/8	11/8	12/8	13/8	14/8	15/8	16/8	17/8	18/8	19/8	20/8	21/8	22/8	23/8	24/8	25/8	26/8	27/8	28/8	29/8	30/8	31/8	1/9
13	Start	End			% Complete
14	1/08/2019	31/08/2019			3%
15	31/07/2019	24/08/2019			18%
16	2/08/2019	20/08/2019			70%
17
Gantt

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P14	1. / Formula is =AND($E14<>"",$I14<>"",P$1>=$E14,P$1<=$E14+$I14*($F14-$E14))	Abc
P14	2. / Formula is =AND($E14<>"",P$1>=$E14,P$1<=TODAY())	Abc
P14	3. / Formula is =AND($E14<>"",P$1>=$E14,P$1<=$F14)	Abc

</div>

 

[halesowenmum]

Thanks very much indeed - I'm sure this will do the trick. But struggling a bit - you reference P14 - am I putting that formula in there and filling it right? Or putting it in the CF rule - or both the spreadsheet cells and the CF rule??

I've tried both and neither is producing coloured squares - what am I doing wrong?

 

[Peter_SSs]

The formula is for Conditional Formatting only, not in the cell itself.

1. Delete any Conditional Formatting that you have.
2. Select P14 and down to the bottom right of the area that might contain colours. For my example that was P14:AV20
3. Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true: =AND($E14<>"",P$1>=$E14,P$1<=$F14) -> Format... -> Fill tab -> Choose colour (green for me) -> OK -> OK
4. Repeat step 3 for the other 2 rules/colours
5. With the area still selected : Conditional Formatting -> Manage Rules -> Check that the colours/formula are in the same order shown in post 2. If they are not, you can select one and use the up/down arrows to re-order them -> Apply -> OK

 

[halesowenmum]

I'm doing better now that I selected the correct range!!

However, having entered all the formulae in the CF and put them in the right order, and test-replicated using your dates and %s, I'm getting the 1st and 3rd colours, but not the amber and I can't understand why....

 

[Peter_SSs]

Can you give me an example of Start & End dates, % Complete and tell me what the first two dates in row 1 (ie P1 and Q1) are?

 

[halesowenmum]

I got it sorted; went thru it all again - confused me for a mo as I took out the dates for weekends and BHs and was stupidly looking for a pattern match with your one!!

I think I'm fully sorted now!

All that's left is to sort out my other query on how you show grouped 'January', 'February' and 'Q1', Q2', and 'W/c xxx' and 'W/c xxx' across columns automatically even if columns are added or removed... may need to lie down in a darkened room before I do that one!!

Thanks for much for the help with this - I always think it's much better to be able to show where you should be not just keep recording where you think you are - doesn't particularly encourage people to think crikey, I'm rather behind I'd better get a wiggle on and I think this shows it visually as they look at their whole plan so I think it will be really useful in use by people who've not done much project management before. The joy!!

 

[Peter_SSs]

I got it sorted;

Thanks for much for the help with this

Good news, and you are welcome.