extracting time value

[carlos]

Hi,

I'm building an application in VBA, which would (among other
funcionalities) calculate the planned time within particular project
between two dates (inserted by user in excel's cells). Is there any
method in MS Project library to calculate this time value, because I
can't find one?

Thanks for any reply.

 

[Jan De Messemaeker]

Hi Carlos,

Application.datedifference
HTH

 

[John]

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates are
related to a schedule in Project, you also mention Excel cells. Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP

 

[carlos]

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates are
related to a schedule in Project, you also mention Excel cells. Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP

Hi,
Thank you for your reply, but that wasn't exactly what I needed. Maybe
I didn't speak myself clear. My task is to get the planned time within
concrete project, between specified dates. For example, between
2005-01-01 and 2005-01-16 there were some planned tasks in project. I
have to find a way to return the planned time (duration) for them from
this period.
I was trying to select these tasks, which start and end between these
specified dates (and sum durations), but it is not enought. Some tasks
start or end sooner or later, than those borders, and my method
doesn't include them.

I would be very gratefull for your help,
Carlos

 

[carlos]

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates are
related to a schedule in Project, you also mention Excel cells. Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP

Hi,
Thank you for your reply, but that wasn't exactly what I needed. Maybe
I didn't speak myself clear. My task is to get the planned time within
concrete project, between specified dates. For example, between
2005-01-01 and 2005-01-16 there were some planned tasks in project. I
have to find a way to return the planned time (duration) for them from
this period.
I was trying to select these tasks, which start and end between these
specified dates (and sum durations), but it is not enought. Some tasks
start or end sooner or later, than those borders, and my method
doesn't include them.

I would be very gratefull for your help,
Carlos

 

[Steve House [MVP]]

You need to explore a bit deeper about what you're trying to accomplish.
The "duration" between two dates in a project is NOT the sum of the
durations of the tasks scheduled during that time interval. The duration
between March 1st and April 1st is the same value whether you have a single
20-days task, a thousand 1-day tasks, or no tasks at all scheduled during
the month. Instead it is the time between those dates that is defined as
working time according to the project calendar regardless of whether any
tasks are scheduled or not. Even if you have multiple tasks scheduled, the
arithmetic sum of their durations doesn't really measure much - it doesn't
give you any idea of the work demands on your resources, for example, or the
costs of the tasks. The function ProjDateDiff() returns the duration
between two dates but I don't think that's what you're really hoping to do
here.
--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Hi,

I'm building an application in VBA, which would (among other
funcionalities) calculate the planned time within particular project
between two dates (inserted by user in excel's cells). Is there any
method in MS Project library to calculate this time value, because I
can't find one?

Thanks for any reply.

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates are
related to a schedule in Project, you also mention Excel cells. Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP

Hi,
Thank you for your reply, but that wasn't exactly what I needed. Maybe
I didn't speak myself clear. My task is to get the planned time within
concrete project, between specified dates. For example, between
2005-01-01 and 2005-01-16 there were some planned tasks in project. I
have to find a way to return the planned time (duration) for them from
this period.
I was trying to select these tasks, which start and end between these
specified dates (and sum durations), but it is not enought. Some tasks
start or end sooner or later, than those borders, and my method
doesn't include them.

I would be very gratefull for your help,
Carlos

 

[Jan De Messemaeker]

Hi Carlos,

Assign any resource to the tasks then Resourc eUsage will show the "Work"
for this resoruce nicely by timescale and that I think that is what you're
after.
HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/index.htm
32-495-300 620

John <[email protected]> wrote in message

Hi,

I'm building an application in VBA, which would (among other
funcionalities) calculate the planned time within particular project
between two dates (inserted by user in excel's cells). Is there any
method in MS Project library to calculate this time value, because I
can't find one?

Thanks for any reply.

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates are
related to a schedule in Project, you also mention Excel cells. Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP

Hi,
Thank you for your reply, but that wasn't exactly what I needed. Maybe
I didn't speak myself clear. My task is to get the planned time within
concrete project, between specified dates. For example, between
2005-01-01 and 2005-01-16 there were some planned tasks in project. I
have to find a way to return the planned time (duration) for them from
this period.
I was trying to select these tasks, which start and end between these
specified dates (and sum durations), but it is not enought. Some tasks
start or end sooner or later, than those borders, and my method
doesn't include them.

I would be very gratefull for your help,
Carlos

 

[JackD]

Carlos,

The first step is to find the tasks which occur during that period. It
sounds like you may have something which finds them, but if not you just
need to find any tasks with a start before the last day of the period you
are looking for, AND a finish after the first day of the period you are
looking at.

if (task.start <= myPeriodEnd) And (task.finish > myPeriodStart) then
'do something
end if

The next thing to do is to read about the TimescaledValues collection and
use it to extract the work within that period for the tasks that occur. You
can sum up the results along the way.


--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

John <[email protected]> wrote in message

Hi,

I'm building an application in VBA, which would (among other
funcionalities) calculate the planned time within particular project
between two dates (inserted by user in excel's cells). Is there any
method in MS Project library to calculate this time value, because I
can't find one?

Thanks for any reply.

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates are
related to a schedule in Project, you also mention Excel cells. Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP

Hi,
Thank you for your reply, but that wasn't exactly what I needed. Maybe
I didn't speak myself clear. My task is to get the planned time within
concrete project, between specified dates. For example, between
2005-01-01 and 2005-01-16 there were some planned tasks in project. I
have to find a way to return the planned time (duration) for them from
this period.
I was trying to select these tasks, which start and end between these
specified dates (and sum durations), but it is not enought. Some tasks
start or end sooner or later, than those borders, and my method
doesn't include them.

I would be very gratefull for your help,
Carlos

 

[carlos]

What I'm trying to accomplish is exactly as you wrote "the sum of the
durations of the tasks scheduled during that time interval". We need
this information, to calculate how much does it take our emloyers to
make these tasks done. Information about time spent for each project
in particular period, we get from MS Access application, where every
employer inserts data about his time spent for each project, that he
is involved. I created a function to calculate this sum of durations
for selected period, but it is very long, slow and inacurate. I also
tried to use TimeScaleValues method for every task in project, but it
doesn't return appropriate values. Maybe i'm doing it wrong, or
TimeScaleValues method is not exactly what i think it is. I'm a real
newbie in MSProject.

You need to explore a bit deeper about what you're trying to accomplish.
The "duration" between two dates in a project is NOT the sum of the
durations of the tasks scheduled during that time interval. The duration
between March 1st and April 1st is the same value whether you have a single
20-days task, a thousand 1-day tasks, or no tasks at all scheduled during
the month. Instead it is the time between those dates that is defined as
working time according to the project calendar regardless of whether any
tasks are scheduled or not. Even if you have multiple tasks scheduled, the
arithmetic sum of their durations doesn't really measure much - it doesn't
give you any idea of the work demands on your resources, for example, or the
costs of the tasks. The function ProjDateDiff() returns the duration
between two dates but I don't think that's what you're really hoping to do
here.
--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Hi,

I'm building an application in VBA, which would (among other
funcionalities) calculate the planned time within particular project
between two dates (inserted by user in excel's cells). Is there any
method in MS Project library to calculate this time value, because I
can't find one?

Thanks for any reply.

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates are
related to a schedule in Project, you also mention Excel cells. Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP

Hi,
Thank you for your reply, but that wasn't exactly what I needed. Maybe
I didn't speak myself clear. My task is to get the planned time within
concrete project, between specified dates. For example, between
2005-01-01 and 2005-01-16 there were some planned tasks in project. I
have to find a way to return the planned time (duration) for them from
this period.
I was trying to select these tasks, which start and end between these
specified dates (and sum durations), but it is not enought. Some tasks
start or end sooner or later, than those borders, and my method
doesn't include them.

I would be very gratefull for your help,
Carlos

 

[Steve House [MVP]]

That's why I asked about what you're trying to do. The sum of the durations
is not "how much time it takes your employees" to get this work done. I
think you should consider using work instead. Duration is the amount of
working time there is between beginning and end. Work is the amount of
working time that was (or will be) used during the same time period. As an
example - Joe works on a single task 1 hour a day for 5 days. He does the
first hour starting Monday at 8am until 9am. He does the last hour on
Friday between 4 and 5 pm. The other three hours are somewhere in between.
Do you want to track that task as being worth 40 hours or 5 hours? If it's
40 hours you are correct using duration. But if its 5 hours, you should be
using work instead. Or I have three resources, Joe, Bill, and Mary, working
together as a team on some task 8 hours a day for 1 week. Do you want to
count that as 5 days or 15 days? Again, the duration is 5 days or 40 hours,
but the work is 15 days or 120 hours.

HTH

--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

What I'm trying to accomplish is exactly as you wrote "the sum of the
durations of the tasks scheduled during that time interval". We need
this information, to calculate how much does it take our emloyers to
make these tasks done. Information about time spent for each project
in particular period, we get from MS Access application, where every
employer inserts data about his time spent for each project, that he
is involved. I created a function to calculate this sum of durations
for selected period, but it is very long, slow and inacurate. I also
tried to use TimeScaleValues method for every task in project, but it
doesn't return appropriate values. Maybe i'm doing it wrong, or
TimeScaleValues method is not exactly what i think it is. I'm a real
newbie in MSProject.

You need to explore a bit deeper about what you're trying to accomplish.
The "duration" between two dates in a project is NOT the sum of the
durations of the tasks scheduled during that time interval. The duration
between March 1st and April 1st is the same value whether you have a
single
20-days task, a thousand 1-day tasks, or no tasks at all scheduled during
the month. Instead it is the time between those dates that is defined as
working time according to the project calendar regardless of whether any
tasks are scheduled or not. Even if you have multiple tasks scheduled,
the
arithmetic sum of their durations doesn't really measure much - it
doesn't
give you any idea of the work demands on your resources, for example, or
the
costs of the tasks. The function ProjDateDiff() returns the duration
between two dates but I don't think that's what you're really hoping to
do
here.
--
Steve House [MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Hi,

I'm building an application in VBA, which would (among other
funcionalities) calculate the planned time within particular
project
between two dates (inserted by user in excel's cells). Is there
any
method in MS Project library to calculate this time value, because
I
can't find one?

Thanks for any reply.

Carlos,
Just to expand upon Jan's reply there are two different ways to work
with date differences. Your post isn't clear about what you want.
Although your post is in an MS Project newsgroup, implying the dates
are
related to a schedule in Project, you also mention Excel cells.
Project
has an expanded method of working with dates compared with Excel. In
Project, the default for date differences is in working time which
considers weekends, holidays and other non-working time defined by the
project's calendar and/or the calendar of resources assigned to tasks
in
Project. On the other hand, Excel date differences are strictly time
deltas based on 24 hour days, 365 days a year.

The Method offered in Jan's reply is for working time, the difference
of
two dates in Project and the full syntax is:
Application.DateDifference(StartDate, FinishDate, Calendar)

However, if what you really want is a date difference in elapsed time,
then you want to use the DateDiff Function which is:
DateDiff(interval, date1, date2, [firstdayofweek, [firstweekofyear]])

Ok, so this is probably more information than you wanted but finding
the
difference between two dates has more than one answer.

Hope this helps.
John
Project MVP


Hi,
Thank you for your reply, but that wasn't exactly what I needed. Maybe
I didn't speak myself clear. My task is to get the planned time within
concrete project, between specified dates. For example, between
2005-01-01 and 2005-01-16 there were some planned tasks in project. I
have to find a way to return the planned time (duration) for them from
this period.
I was trying to select these tasks, which start and end between these
specified dates (and sum durations), but it is not enought. Some tasks
start or end sooner or later, than those borders, and my method
doesn't include them.

I would be very gratefull for your help,
Carlos