Determine percentage of target based on two dates

I'm trying to track a metric regarding meeting artifact delivery committments and need assistance in calculating the percentage between two dates - Target Date and Delivery Date.

Metrics
Marginal : metric less than .90
Review : metric between .90 - .93
Effective : metric between .94 - .97
Good : metric between .98 and 1.00
Outstanding : 1.00 plus 10% of commitments delivered ahead of schedule


Any thoughts on how this might be accomplished?

So what are some examples of inputs and some examples of expected outputs?

You have provided a glimpse into what you need but I'm having trouble reconciling the fact that you want a "percentage between two dates" vs. the rating scale you provided.

Are Target Date and Delivery Date both relative to the same Start Date? Or is there also a Target Start and and Actual Start?

Once you have the percentage for a specific event, do you want to rate each one individually on the scale you provided? Or do you want to average them all to get a single rating? Or do you you simply want to determine a percentage of how many events completed on or ahead of schedule?

The way that I would calculate a percentage is

(Actual Finish - Actual Start)/(Target Finish - Target Start)

This is just straightforward arithmetic using dates
=(A2-A1)/(B2-B1)

If the Actual Start is always the same as the Target Start then you can adjust the formula accordingly
=(A2-A1)/(B2-A1)

However, this is upside down from your rating scale. .90 means you completed the task in 90% of what was planned but you call that "marginal", and 1.00 (exactly on time) is "Outstanding".

Is your metric actually the percentage of tasks that completed before the target date?

I'm really interested in the accuracy for delivering on time, thus my focus has been on the target date (when I said I would deliver) vs. the complete date (the date I did deliver). Here is more detail:

For example, if the Target column date was 03/31/11 and the Complete
column date 03/31/11, then the Metric column would read as "1.00". If
the Complete column was any date sooner than the Target date, the
Metric would be higher than "1.00"; for a Complete date later than the
Target date, such as 04/06/2011, then the Metric would be under than
"1.00"


Target = K4 (mm/dd/yy)
Complete = L4 (mm/dd/yy)
Metric = M4 (0.00%)


NOTE: My metrics have been provided to me, now I'm trying to determine how to capture that information. Perhaps I need to adjust my approach. I've attached a partial screenshot of my spreadsheet.

To calculate your metric we will use

(Target Duration-Actual Duration)/Target Duration

or

=((K4-J4)-(L4-J4))/(K4-J4)

This looks like a job-type of thing, so I'm going to suggest you count only working days for durations.

=(NETWORKDAYS(J4,K4)-NETWORKDAYS(J4,L4))/NETWORKDAYS(J4,K4)

1.0 means you are exactly on target; lower means worse, higher means better. (This is analogous to the Schedule Performance Index used in Earned Value Measurement System.)

Is that what you want to show up in your Metric column? Or do you want the word that indicates the range that this number falls into? The problem with "Outstanding" is that it aggregates multiple items, and I don't know what you want to aggregate. (The others are meaningful just for a single item.)

As NBVC noted it would help if you could show what results you are looking for.

Thanks for posting your solution,

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

PWinkz, unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.