Number of workdays between two dates

[KatJ]

I'd like to work some customised data into my project file and am not sure
where to start. I've been asked to automate some reporting against Start,
Finish and a custom date field Estimated Finish Date (Date1 with the name
updated). I need to set status against the following scenarios:

1) Green if the duration between a task's Start and Estimated Finish Date is
less than or equal to the duration between the task's Start and Finish Date
2) Amber if the duration between a task's Start and Estimated Finish Date is
between 0% and 10% greater than the duration between the task's Start and
Finish Date
3) Red if the duration between a task's Start and Estimated Finish Date is
greater than 10% longer than the duration between the task's Start and Finish
Date

So, if the task with the following data should be Red
Start - 7 Sept 09
End - 11 Sept 09
Estimated Finish Date - 12 Sept 09

I have some ability in Excel and can do this very readily if I extract the
project data. However, I need to do this in 15 different plans so to avoid
lots of manual work I was hoping I could do it within Project itself.

Based on Excel knowledge, I thought that I might be able to use a customised
field to capture the workdays between Start and Estimated Finish, then use a
traffic light to compare the new duration to the original duration, based on
the status criteria above. This grand plan has failed at the first step as I
can't figure out how to calculate the duration.

Can anyone help me out with this? I have Project 2003 (no access to Project
Server)

Thanks in advance for any assistance!

 

[Rob Schneider]

This won't be as hard to do as you might think... However, first I need
to ask you:

In your model of the projects in Project MPP files, what are the
"columns" (or fields) you are using to represent what you "Start",
"End", and "Estimated Finish Date"? I ask because "End" and "Estimated
Finish Date" are not standard field names.

My hunch is we need to re-orientate how you are using Project? For
example, some of this sort of insight about status is already in Project ...


--rms

www.rmschneider.com

 

[KatJ]

Thanks Rob. Yes, I should have checked my memory. "End" should actually
read "Finish" and it is the standard field (meaning I do have Duration
already set up in the default view).

In my attempt to sort this issue, I've added an extra date column (Date1
from the column list) and renamed it Estimated Finish Date. So apart from
being a date field, there is no apparent additional functionality.

I mentioned that there are 15 project plans. They have several owners (many
with fewer project skills than me!) so I'm looking for the simplest option
that will enable them to tell me their original plans and then indicate where
those plans are going awry. Having a limited understanding of the baseline
functionality I've probably gone down an inappropriate route in response to
this!

I'm a project novice so am more than happy to take advice on how to set it
up appropriately for what I want to do. If there's an easier way I'd really
appreciate hearing it

 

[Rob Schneider]

Well, the good news is that to do exactly what you want is relatively
easy. See below. However, the bad news is that I have a strong *hunch*
that your "models" of the projects in Project are not done in a way
which would really help your projects, and you might find that Project
is "getting in the way". Using Project as designed, for example, and
with a properly constructed MPP file you can let Project give you early
warning of projects "gone awry" ... perhaps even before the Owners even
notice it! Do this by letting Project compute the forecast end dates,
compare against your deadlines, etc. Use Project's "indicator" field to
give some indications. Etc. I think using Project in a better way should
be of great value. In the interest of improving the odds of successful
projects, it might be worthwhile having a read of one of the many great
books on Project, taking course, or getting some help to set this all up.

To make these indicators:

Menu: Tools/Customize/Field

Note there is now a Help button you can press to go straight to a
comprehensivie Help article.

You want to create a new task field of type "number". Construct the
algorithm (you've specified below), with "IIF() and date arithmetic
functions using the Project fields [Duration], [Start], [Date1] etc. to
compute a number, e.g. 1, 2, or 3 based on the conditions you state.

Then, based on the algorithm returning a 1, 2, or 3, use the "graphical
indicator" button (bottom of dialog box) to test for the value of the
number field and display the indicator you want. You'll find some
coloured "smiley face" images in red, amber, and green which will work.
Or just use the coloured balls.

They key is to construct the IIF() function to return 1, 2, and 3.

However, that being said, I have concerns about using this approach to
detect or even report on projects "gone awry".



--rms

www.rmschneider.com

 

[José Miguel Piñeres]

Hello Katj,

If I understood you well, basically what you are trying to get is the
difference in working days between the finish date and the Estimated Finish
(Custom Field) of a task.

You can achieve this by using the function "ProjDateDiff" in a Duration type
custom field. This function uses the following syntax:

ProjDateDiff( date1, date2, calendar )

This allows you to give it not only the two dates, but also specify what
calendar you want to use to calculate the difference in working days.

So based on your example, you lets say you use the Duration1 field with the
following formula:

ProjDateDiff( [Finish], [Estimated Finish])

The result will be "1 day" if the following assumptions are true:

1.- That the Standard calendar has the following workweek settings: Monday
through Saturday, 8 a.m. - 12 p.m. and 1 p.m. - 5 p.m.
2.- That the Finish date (and time) is 11 Sept 09 at 5 p.m.
3.- That the Estimated finish date (and time) has been set to 12 Sept 09 at
5 p.m.

Now you can use that value in another duration field that will calculate the
total amount of days between the Start date and the Estimated finish date of
the task ([Duration]+[Duration1]) and make it show the graphical indicators
as you want.

I hope this has been useful to you. Let us know how you get along with it.

Best regards,

 

[Steve House]

I wonder how you're coming up with that "Estimated Finish Date" and why it
would ever be different from the "Finish" date? Duration is "estimated"
until all the work is done. Finish is Start + Duration. If you originally
estimated a duration of 10 days, let say, so the task would finish on 15 Sep
(just to pick something from thin air for purpose of discussion) but now you
think it's going to finish on 18 Sep, isn't that the same thing as revising
the (estimated) duration from 10 days to 13 days? Enter the revised
duration estimate and the finish date recalculates or if work is in
progress, in the tracking table enter Actual Duration so far and an
estimated Remaining Duration and you accomplish the same thing. If you're
doing all this so you can track actual performance versus intial estimates,
well that's what a baseline is for. Your schedule is dynamic but the
baseline is static ... save it before starting work and it preserves your
initial estimates for comparison purposes.

 

[Rob Schneider]

See Steve's more detailed response. He's getting into the details I was
pointing you towards!

Bottom Line: Learn how to have Project do it's thing. It can do a lot
of work for you with lots of benefits that will help the projects.

--rms

www.rmschneider.com

Well, the good news is that to do exactly what you want is relatively
easy. See below. However, the bad news is that I have a strong *hunch*
that your "models" of the projects in Project are not done in a way
which would really help your projects, and you might find that Project
is "getting in the way". Using Project as designed, for example, and
with a properly constructed MPP file you can let Project give you early
warning of projects "gone awry" ... perhaps even before the Owners even
notice it! Do this by letting Project compute the forecast end dates,
compare against your deadlines, etc. Use Project's "indicator" field to
give some indications. Etc. I think using Project in a better way should
be of great value. In the interest of improving the odds of successful
projects, it might be worthwhile having a read of one of the many great
books on Project, taking course, or getting some help to set this all up.

To make these indicators:

Menu: Tools/Customize/Field

Note there is now a Help button you can press to go straight to a
comprehensivie Help article.

You want to create a new task field of type "number". Construct the
algorithm (you've specified below), with "IIF() and date arithmetic
functions using the Project fields [Duration], [Start], [Date1] etc. to
compute a number, e.g. 1, 2, or 3 based on the conditions you state.

Then, based on the algorithm returning a 1, 2, or 3, use the "graphical
indicator" button (bottom of dialog box) to test for the value of the
number field and display the indicator you want. You'll find some
coloured "smiley face" images in red, amber, and green which will work.
Or just use the coloured balls.

They key is to construct the IIF() function to return 1, 2, and 3.

However, that being said, I have concerns about using this approach to
detect or even report on projects "gone awry".



--rms

www.rmschneider.com

Thanks Rob. Yes, I should have checked my memory. "End" should
actually read "Finish" and it is the standard field (meaning I do have
Duration already set up in the default view).

In my attempt to sort this issue, I've added an extra date column
(Date1 from the column list) and renamed it Estimated Finish Date. So
apart from being a date field, there is no apparent additional
functionality.

I mentioned that there are 15 project plans. They have several owners
(many with fewer project skills than me!) so I'm looking for the
simplest option that will enable them to tell me their original plans
and then indicate where those plans are going awry. Having a limited
understanding of the baseline functionality I've probably gone down an
inappropriate route in response to this!

I'm a project novice so am more than happy to take advice on how to
set it up appropriately for what I want to do. If there's an easier
way I'd really appreciate hearing it

 

[Steve House]

And for KatJ, I forgot to mention in my original post that calculation of
the revised estimate versus the original estimate is already "standard
equipment." The already defined Finish Variance field, included on the
Variance table, is the difference between the currently scheduled (ie,
presently estimated) finish and the baseline (ie, originally estimated)
finish. No offense intended, Kat, but it sounds like you're trying to
reinvent the wheel here.

--
Steve House
MS Project Trainer & Consultant

See Steve's more detailed response. He's getting into the details I was
pointing you towards!

Bottom Line: Learn how to have Project do it's thing. It can do a lot of
work for you with lots of benefits that will help the projects.

--rms

www.rmschneider.com

Well, the good news is that to do exactly what you want is relatively
easy. See below. However, the bad news is that I have a strong *hunch*
that your "models" of the projects in Project are not done in a way which
would really help your projects, and you might find that Project is
"getting in the way". Using Project as designed, for example, and with a
properly constructed MPP file you can let Project give you early warning
of projects "gone awry" ... perhaps even before the Owners even notice
it! Do this by letting Project compute the forecast end dates, compare
against your deadlines, etc. Use Project's "indicator" field to give
some indications. Etc. I think using Project in a better way should be of
great value. In the interest of improving the odds of successful
projects, it might be worthwhile having a read of one of the many great
books on Project, taking course, or getting some help to set this all up.

To make these indicators:

Menu: Tools/Customize/Field

Note there is now a Help button you can press to go straight to a
comprehensivie Help article.

You want to create a new task field of type "number". Construct the
algorithm (you've specified below), with "IIF() and date arithmetic
functions using the Project fields [Duration], [Start], [Date1] etc. to
compute a number, e.g. 1, 2, or 3 based on the conditions you state.

Then, based on the algorithm returning a 1, 2, or 3, use the "graphical
indicator" button (bottom of dialog box) to test for the value of the
number field and display the indicator you want. You'll find some
coloured "smiley face" images in red, amber, and green which will work.
Or just use the coloured balls.

They key is to construct the IIF() function to return 1, 2, and 3.

However, that being said, I have concerns about using this approach to
detect or even report on projects "gone awry".



--rms

www.rmschneider.com

Thanks Rob. Yes, I should have checked my memory. "End" should
actually read "Finish" and it is the standard field (meaning I do have
Duration already set up in the default view).

In my attempt to sort this issue, I've added an extra date column (Date1
from the column list) and renamed it Estimated Finish Date. So apart
from being a date field, there is no apparent additional functionality.

I mentioned that there are 15 project plans. They have several owners
(many with fewer project skills than me!) so I'm looking for the
simplest option that will enable them to tell me their original plans
and then indicate where those plans are going awry. Having a limited
understanding of the baseline functionality I've probably gone down an
inappropriate route in response to this!

I'm a project novice so am more than happy to take advice on how to set
it up appropriately for what I want to do. If there's an easier way I'd
really appreciate hearing it

:

This won't be as hard to do as you might think... However, first I need
to ask you:

In your model of the projects in Project MPP files, what are the
"columns" (or fields) you are using to represent what you "Start",
"End", and "Estimated Finish Date"? I ask because "End" and "Estimated
Finish Date" are not standard field names.

My hunch is we need to re-orientate how you are using Project? For
example, some of this sort of insight about status is already in
Project ...


--rms

www.rmschneider.com





KatJ wrote:
I'd like to work some customised data into my project file and am not
sure where to start. I've been asked to automate some reporting
against Start, Finish and a custom date field Estimated Finish Date
(Date1 with the name updated). I need to set status against the
following scenarios:

1) Green if the duration between a task's Start and Estimated Finish
Date is less than or equal to the duration between the task's Start
and Finish Date
2) Amber if the duration between a task's Start and Estimated Finish
Date is between 0% and 10% greater than the duration between the
task's Start and Finish Date
3) Red if the duration between a task's Start and Estimated Finish
Date is greater than 10% longer than the duration between the task's
Start and Finish Date

So, if the task with the following data should be Red
Start - 7 Sept 09
End - 11 Sept 09
Estimated Finish Date - 12 Sept 09

I have some ability in Excel and can do this very readily if I extract
the project data. However, I need to do this in 15 different plans so
to avoid lots of manual work I was hoping I could do it within Project
itself.
Based on Excel knowledge, I thought that I might be able to use a
customised field to capture the workdays between Start and Estimated
Finish, then use a traffic light to compare the new duration to the
original duration, based on the status criteria above. This grand
plan has failed at the first step as I can't figure out how to
calculate the duration.

Can anyone help me out with this? I have Project 2003 (no access to
Project Server)

Thanks in advance for any assistance!