Reporting Progress and % Complete Using Excel

[Chris Jackson]

Hi Everyone,

I have a request to report progress complete against plan using Excel. The attached file shows what I am trying to achieve as the end game but essentially:

1) Each Work Package has a start and finish date
2) The sheet will colour the "%Forecast Bar" if the date in the header (Row 5) is within the start and finish date specified.
3) The % Complete is the % complete of the package as a % of the start and finish dates and will populate the sheet with a value (that I can assign conditional formatting to). This can be rounded up/down.
4) The % Spend is another manually entered value that works identical to the % Complete and populates the sheet with a value in bar form to the progress.

Ideally I need the whole thing to resemble a Gantt but I cannot think of a way to populate the columns based on the % complete.

So I was thinking along the lines of:

61% = fill in as many boxes with a "#" IF the date in Row 5 is less than or equal to 61% of the value of End Date - Start Date.

So if I had a start date of 01/01/18 and an end date of 20/01/18 then 61% would populate the row with a "#" for each date that is less than or equal to 61% of the range between 01/01/18 and 20/01/18 (so that would be 01/01 through to 12/01/2018)

Hopefully that makes sense in conjunction with the sample attached.

If it cannot be done, it cannot be done but it certainly looks feasible, just beyond my range

Thanks in advance

Chris

 

[NARAYANK991]

Hi ,

See the attached file , where this has been done for Work Package 1.

Narayan

 

[vletm]

Chris Jackson
... or test this too.
If same layout then You could add as many 'Work Packages' as needed.
Press [ReFresh]-button.
The 1st day of Gnatt have to set to cell [D1].
After that all dates, weeks and months would refresh too while [ReFresh].
Blue values should fill manually.
Default Gnatt-colors are right side of [ReFresh]-button.
Those %Progress maybe needs some modifications...

 

[Chris Jackson]

Hi ,

See the attached file , where this has been done for Work Package 1.

Narayan

Thanks Narayan, I'll give it a try today and see how I get on and post my feedback.

 

[Chris Jackson]

Chris Jackson
... or test this too.
If same layout then You could add as many 'Work Packages' as needed.
Press [ReFresh]-button.
The 1st day of Gnatt have to set to cell [D1].
After that all dates, weeks and months would refresh too while [ReFresh].
Blue values should fill manually.
Default Gnatt-colors are right side of [ReFresh]-button.
Those %Progress maybe needs some modifications...

Thanks vletm, this almost does what I need, the only thing is that it treats the % Spend and the % Complete the same. Is it possible that the button can adjust both of those rows separately?

Great work though. really classy!

 

[vletm]

Chris Jackson
as written Those %Progress maybe needs some modifications...
There were typo with those % - is it better now/Okay?.
... and same time I noticed one more.
Do it all that You need now?

 

[Chris Jackson]

That looks great vletm, that really does the trick

It's only a minor thing but when you change the dates, the bars should extend the grey to the end date, with the % Forecast shading blue to the % it has achieved, the % Complete to its place and % Spend to its place.

I can see that the % Complete and % Spend shade to the right spot but their Grey does not adjust on refresh and the % Forecast shades all boxes to the end date (and adjusts the Grey). does that make sense at all?

I have to say though, I'm impressed with what it does

Regards

Chris

 

[vletm]

Chris Jackson
Test this version ...
There are more checks with those non-black-font-cells.
Some of those would be critical.
You can also select 'the 1st day of chart by selecting any 'gray-day'.
The whole chart will always refresh ( also after change of dates or %Progress ).
If there will be a lot of 'Work Packages' ... I haven't test.
You'll test ...