% complete versus planned level of progress with conditional formatting + proximity

Hi

I want to be able to show a data bar for the % complete such that if someone reports they've '50% of the way through' but, when you compare today's date with the planned due date, that item should actually be 80% complete (and is therefore behind) so it would be clear which items were behind. What would be the best combo of formula / CF that you would recommend? What would the formula be in the cell, and what would it be in the CF field?

AK9 contains the TODAY() date
AL9 will be where we'll record % complete from the point of view of the item owner (should I put this as a % format?)
AM9 planned due date

In a further column or in the % complete column (not too fussed) I'd like to be able to show with colour whether the item is hugely behind, a bit behind, or basically on track as planned. Ideally it would be aligned to the result of the above calculation so you could see through the use of colour/data bars for eg, which are really behind, a bit behind, or pretty much on track. I'm not sure how to explain that better and can only hope it makes sense - that relationship of actual progress against planned progress by this date is what's key far more so than it is to say 'oh well you've only got 4 days left' - the number of days actually doesn't really matter anywhere near as much as the actual versus planned does.


I'd also like, within my Due Date column, to be able to show the following:
Red - only 5 or less working days left between today's date in col AK and due date in col AM
Amber - only 6 - 10 working days left between today's date in col AK and due date in col AM
Green - 11 or more working days left between today's date in col AK and due date in col AM.
There's no relationship in the above at all to any % complete/progress elements, this is just to do with the basic no. of days left side of things.
Again I'd need a formula for in the cell and one for CF (assuming that's what would be recommended). Just an appropriately highlighted cell fill colour would do for this.

Be great to hear back with some thoughts and possible solutions.

Very many thanks.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Hi
I hadn't attached a file as it was just the three cells with some very basic data types plus, I can't include 'manually calculated results' because that's the formula I'm asking about but here's the very basic table. I've endeavoured to attach, gonna try submitting the post and see if I've got it done ok.

Attached has the CF

For the check on % completion you need the start date so:

(Today()-Start date)/(Due Date - Start Date)

Very simplistic as it assumes linear progression.

Oooh, thank you for this.

Ok so let me understand what it's doing - in the eg where it's 50% reported progress is your column F saying that whilst the person estimates that they're 50% there, they should be 58% there by now - so they're behind (apologies, been a very long day).

What I'd also like to be able to do is smack folks between the eyes (not literally) with some colour coding within that column such that:
- Green would be anything better than 10% behind schedule.
- Amber would be items between 10% and 25% behind schedule.
- Red would be those more than 25% behind schedule.

Now I did in the interim work with a colleague and he said "It can't be done with the =TODAY() formula" and "It can't be done with CF" (!) and he did some VBA for me. However, I'm not convinced it's working correctly. When he did it he told me after that if I dared to change any CF I had in the document it would "totally mess everything up" which is a worry in a way because, further CF may well be added - that's just the way documents get developed so I'm wondering is my normal development of this document has thrown it out. I can share that on here it that's of use.

The VBA also covered off the following:
- If progress was 0% it would show in red, but without a bar
- If the Due Date changed (was extended or brought forward), the data bar would still react appropriately (it's not just the % complete that can tell you if you're behind or not, due dates can be extended or brought forward so it was made to work from either).

With it being VBA it did it everything directly in the % Complete column - bars and coloration of - (although I'm not averse to having to have two cols as in your example). I always prefer simpler the better but I do need to really make it stand out so that if they're behind from where they actually should be for the today's date then it shows in the colour of the bars then they know whether they need to get their skates on or not!

If you data bars solution can do this I'm very happy to go with that, otherwise be happy to share the VBA with you as an alternative approach if required.

I've just had chance to tidy up the document in order to share it on here. Please do take a look and give me your thoughts as to the VBA + CF combo as it doesn't look to be working to me.
For info, the person who built this for me their VBA looks at today's date, due date, how many days since start, total duration of days in the given period, time elapsed as a %, difference as a % and I believe works off of that premise.

Thank you - yes I have sent a message to ask for this thread to be moved.

Would you have a sec to reply to the following query?:
Ok so let me understand what it's doing - in the eg where it's 50% reported progress is your column F saying that whilst the person estimates that they're 50% there, they should be 58% there by now - so they're behind (apologies, been a very long day).

Hi

I would have no idea whether a % completed could/couldn't be calculated using =TODAY().....

What this is all about is the need to show, as of today where SHOULD you be % complete-wise with this item, and where ACTUALLY ARE YOU - is there disparity (behind (negative situation) or ahead (positive situation), or not. And of course often there is a disparity between where you should be and where the person reports they are.

So it's today's date and the due date which are the parameters to work within because we're interested in how much time have you got left now to complete this item - it's a countdown if you like, not a calculation between two set dates where the start date is in the past - the 'start date' is always today, always. The no. of (working days preferably) left between today and the due date is what we have to work with. It's the relationship between today and the due date is what we're interested in.

So let's say for an item there's 100 days left between today's date and the date of that meeting and the item owner says 'I'm 50% of the way through' but he should actually be 75% of the way through given the remaining no. of days, when he entered 50 into the % complete column, it would record his 50% and display it, but then he'd get visual feedback from an IN CELL DATA BAR using red amber or green which alerts any disparities ie "ahem, that's behind buddy, and it's behind enough that I'm going to make it amber or red so that person knows it".

If the item is ahead of the game in terms of % completed that's been entered, or 100%, it would always be green.


=TODAY() is used because I've got multiple people going into this document making edits and updates. I can't have it that I've got to go in every single day for the next 2 years of this IT implementation Programme I'm working on, and manually update all the cells to show today's date - hence my initial use of the =TODAY() formula. And if =TODAY() really cannot work and it needs to be a manual date, then the guy who did the VBA for me was right and that formula can't be used. But neither can it be a date I have to enter manually!

Taking the descriptive notes the VBA guy gave in the VBA code he wrote, this is what his does (although again, it ain't working quite right from what I can see):
- Specifies a data bar to go into the relevant column
- Calculates % time elapsed
- Calculate % difference between time elapsed and due date
- Changes the data bars based on the % difference.
I'm not experienced in VBA but he somehow has some line of code in there that inputs/creates (whatever, however) today's date as a manual date in the cells of the relevant col for this. I open the doc and without =TODAY() I always see today's date.

Unfortunately whatever method I've used to 'contact the moderators' clearly hasn't been the correct one as I've not been able to get this thread moved - is there a specific link where I can do that successfully?

As ever, all thoughts from multiple brains / info on actually how to get this thread moved would be very much appreciated. Many thanks indeed.

Demonstrates the reasons why not to mix VBA with on sheet formatting/formula.
Use one or the other, not a 'mis-mash' of both - you have interference of your troublesome cell with a conflict in the code.
Also the conditional formatting was working with a cell formatted % so calc was a % of % never getting above one.
Simply formatted numerically with the correct application to bands now(0 to 60=red, 61 to 90=orange, 90 to 100=green)
The VBA affecting the cell is iterated out and the last sheet reference should set recalculate (which did not appear to be happening in the Worksheet_Change event)
Tip of the iceberg, I am surprised that the app ever worked properly.
torachan.

I don't/didn't want to or intend to use two approaches - I'm just trying to get to a solution - that's it. I'm reliant on what I'm told/given by others unfortunately - if I could do it all myself I obviously would. I noted that the VBA just didn't work - and when I'd first given the spreadsheet to my VBA man I'd not put it as a %, I'd just put it as a plain number value - he changed it to calculate on %s and wrote the VBA accordingly.

Ok what I'm trying to get to is, there's a finite amount of time with a final date by which something must be done 100%. The timespan to the due date is from today always because it's a countdown. There is a minimum amount of progress you'd expect reasonably at any given point. And maybe it's more based on the relationship of the % progress reported and the due date only, and today's date is irrelevant, happy to say that the today's date may be a red herring if you think it is.

We're hoping for 100% complete on or before the due date. If on the date they enter their % complete figure, between today and that due date they report 10% progress but the item should reasonably at this point (based on these dates/how close the due date is) be at more like 50% the data bar would show red or amber as appropriate - they're not making the progress they should make given how close the due date is.

HI all, am seeking to find if anyone is able to review the above document so I could apply it and see if it renders my % progress bar working?!

Many thanks.

Hey all (incl. AliGW) to see if anyone it able to assist with the % progress data bar query?