OT RAG formula

[J.Allman]

SO I want my team managers to easily identify tasks that are running behind
that CAN be corrected and completed before BASELINE finish of the task in
question. The idea is that at the most a resource can work an additional 2
hours per day to make up the variance. What I am trying to identify is the
number of hours remaining in the work to complete the task that is running
behind and divide that number by the number of days I have left from [current
date] until I hit the baseline finish for that task.
In the formula below:
1= between .1 and 2 hours of overtime per day = achievable
3 = above 2 hours of overtime per pay = doubtful
8 = the formula returns a calculation of 0 or negative which means that the
current date has already passed the baseline finish or is the same day as the
baseline finish and thus no amount of overtime can get the task to finish by
baseline finish = impossible


IIf(([Work]-[Actual Work])/([Baseline Finish]-[Current
Date])>2,3,IIf(([Work]-[Actual Work])/([Baseline Finish]-[Current
Date])>0,1,IIf(([Work]-[Actual Work])/([Baseline Finish]-[Current
Date])<=0,8)))

So far I cant get the formula to work properly. I am guessing because I am
really bad at coding my formulas..if I had to guess the logic is good but I
am missing something like [Minutes Per Day] in the formula.

Can I get some help?
I also just realized I probably need to add something to the front of this
formula to exclude any tasks that have been completed so I can avoid running
into a numerator = 0 in my formula and thus get an "ERROR" or something.

 

[J.Allman]

I just realized a couple of things:
The formula below simply represents:
[remaining work]/number of days between current date and baseline finish

Assuming the formula below got that much right I need to adjust my <> in the
formula by adding regular number of hours worked to the current numbers in
the formula (instead of <0, <2, and > 2 it needs to be < 7, <9, and > 9...you
get the jist)

however this presents new issues:
1. I dont think the formula accomodates for nonworking days like weekends
and holidays
2.i dont think the formula accomodates resources working at less than 8
hours per day.

this is not looking good

 

[Jim Aksel]

I have some good news and some bad news. The formula does respect working
days. Work (and therefore differences between two work values) will be in
minutes. durations for your denominator will also be in minutes. So, your
ratios are carrying consitent units.

But, remembering that no good deed can ever go unpunished, your formula does
not consider available hours of the assigned resource(s) and there could be
more than one resource assigned. Two or more of those resources may work odd
shifts (including 4-10's or part time). We're going to have some head
scratching to do here becuase normally, availabitly of 100% is still going to
limityour troops to 8 hours per day. You can handle "uncompensated OT
availability" by increasing availability for specific dates on the Resource
Sheet. Besides, your solution may be even easier if you can crash the
duration by throwing resources at it...the classic 9 women can have a baby in
a month.

Also, we need to consider that you may authorize Saturday Work on certain
tasks that are in danger of not completeing.

And while we're here, you make the tacit assumption that 1 hour of work
equates to 1 hour of progress. You should be adjusting these estimated
remaining hours by the Schedule Peformance Index to get a more rigorous
solution.

Some additional bad news. Your formula needs to also consider situations
where [Start] is already to the right of [Baseline Finish], or at least
[Current Date] or [Status Date] is to the right of [Baseline Finish].

With all these things on the table, I suggest you tackle this with a Macro,
not a formula.

As it turns out, I may have something that can help you. Ordinarily this
type of thing is not "Freeware" but I can certainly get you something to get
you started. Try contacting me off line and I'll see what I can do. Search
the link below for contact info.

You're in luck, my wife is out of town this weekend and I am looking for a
mental challenge on Saturday. I'd love to discuss this with you and give you
some additional input.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

I just realized a couple of things:
The formula below simply represents:
[remaining work]/number of days between current date and baseline finish

Assuming the formula below got that much right I need to adjust my <> in the
formula by adding regular number of hours worked to the current numbers in
the formula (instead of <0, <2, and > 2 it needs to be < 7, <9, and > 9...you
get the jist)

however this presents new issues:
1. I dont think the formula accomodates for nonworking days like weekends
and holidays
2.i dont think the formula accomodates resources working at less than 8
hours per day.

this is not looking good

SO I want my team managers to easily identify tasks that are running behind
that CAN be corrected and completed before BASELINE finish of the task in
question. The idea is that at the most a resource can work an additional 2
hours per day to make up the variance. What I am trying to identify is the
number of hours remaining in the work to complete the task that is running
behind and divide that number by the number of days I have left from [current
date] until I hit the baseline finish for that task.
In the formula below:
1= between .1 and 2 hours of overtime per day = achievable
3 = above 2 hours of overtime per pay = doubtful
8 = the formula returns a calculation of 0 or negative which means that the
current date has already passed the baseline finish or is the same day as the
baseline finish and thus no amount of overtime can get the task to finish by
baseline finish = impossible


IIf(([Work]-[Actual Work])/([Baseline Finish]-[Current
Date])>2,3,IIf(([Work]-[Actual Work])/([Baseline Finish]-[Current
Date])>0,1,IIf(([Work]-[Actual Work])/([Baseline Finish]-[Current
Date])<=0,8)))

So far I cant get the formula to work properly. I am guessing because I am
really bad at coding my formulas..if I had to guess the logic is good but I
am missing something like [Minutes Per Day] in the formula.

Can I get some help?
I also just realized I probably need to add something to the front of this
formula to exclude any tasks that have been completed so I can avoid running
into a numerator = 0 in my formula and thus get an "ERROR" or something.