Prev: getting following error messages in project 2003
Next: Project 2003 integration with Sharepoint 3.0
From: Sue on 24 Aug 2006 11:18
Is there a way that I can format task Start and finish dates to display the
number of working days from the start of a project?

For example,
Start: 8/9 Finish 8/11 shows as Day 1 - Day 3
Start: 8/14 Finish 8/21 shows as Day 4 - Day 9

I want the day numbers to take into account non-working days from the
project calendar. This is different than changing the timescale in the Gantt
view to Day1, Day2 because the timescale does not adjust for non-working
days. I have tried using a formula in a custom field to calcuate the
difference between the date and the project start date, but that method also
does not account for non-working days (at least not that I could find).

Any help is greatly appreciated.

Make it a great day!
From: John on 24 Aug 2006 11:52
In article <69299AD0-CCC6-471A-866F-9D9B7C863941(a)microsoft.com>,
Sue <Sue(a)discussions.microsoft.com> wrote:

> Is there a way that I can format task Start and finish dates to display the
> number of working days from the start of a project?
>
> For example,
> Start: 8/9 Finish 8/11 shows as Day 1 - Day 3
> Start: 8/14 Finish 8/21 shows as Day 4 - Day 9
>
> I want the day numbers to take into account non-working days from the
> project calendar. This is different than changing the timescale in the Gantt
> view to Day1, Day2 because the timescale does not adjust for non-working
> days. I have tried using a formula in a custom field to calcuate the
> difference between the date and the project start date, but that method also
> does not account for non-working days (at least not that I could find).
>
> Any help is greatly appreciated.
>
> Make it a great day!

Sue,
There are two formulas available for finding the difference between two
dates.
ProjDateDiff(date1, date2, calendar)
DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)

You apparently used the first formula which does give the difference in
working days. What you want is the second formula. It gives the
difference in calendar days.

Hope this helps.
John
Project MVP
From: Sue on 25 Aug 2006 13:09
Thank you ! I may have asked the question wrong, but since you gave me both
formulas, I got exactly what I was looking for.

Here's what I did to get what I needed (for anyone else who needs a little
more detail)

I first created a custom number field with the formula John gave me:
[Number10] = (ProjDateDiff([Project Start],[Start],"Standard")/480)+1

This compares the start date to the project start date and adjusts the value
to the number of days (which is the division by 480)

Using this formula, when your projects starts on a Monday, the first task
will be on Day 1. The next Monday will be Day 6. Working days are based on
what is defined in the "standard" calendar.

I did the same thing to compare the finish date to the project start
[Number11] = (ProjDateDiff([Project Start],[Finish],"Standard")/480)+1

Then I created a little formula to merge thetwo custom fields into a custom
text field:
[Text10] = IIf([Number10]=[Number11],"Day " & [Number10],"Day " &
[Number10] & " - Day " & [Number11])

So now I have a nice little timeline that looks like:

Task 1 Day 1
Task 2 Day 1 - Day 4
Task 3 Day 4 - Day 5
Task 4 Day 6

You can substitute the formulas for [Number10] and [Number11] and get the
same thing, but I like to break it up just to keep it simpler to troublewhoot
if something doesn't look right.

Sue


"John" wrote:

> In article <69299AD0-CCC6-471A-866F-9D9B7C863941(a)microsoft.com>,
> Sue <Sue(a)discussions.microsoft.com> wrote:
>
> > Is there a way that I can format task Start and finish dates to display the
> > number of working days from the start of a project?
> >
> > For example,
> > Start: 8/9 Finish 8/11 shows as Day 1 - Day 3
> > Start: 8/14 Finish 8/21 shows as Day 4 - Day 9
> >
> > I want the day numbers to take into account non-working days from the
> > project calendar. This is different than changing the timescale in the Gantt
> > view to Day1, Day2 because the timescale does not adjust for non-working
> > days. I have tried using a formula in a custom field to calcuate the
> > difference between the date and the project start date, but that method also
> > does not account for non-working days (at least not that I could find).
> >
> > Any help is greatly appreciated.
> >
> > Make it a great day!
>
> Sue,
> There are two formulas available for finding the difference between two
> dates.
> ProjDateDiff(date1, date2, calendar)
> DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)
>
> You apparently used the first formula which does give the difference in
> working days. What you want is the second formula. It gives the
> difference in calendar days.
>
> Hope this helps.
> John
> Project MVP
>
From: Sue on 25 Aug 2006 13:23
*Correction*

The finish date formula is not quite correct. It should not be adjusted by
one day, since it is at the end of the day, not the beginning.
The correct formula is
[Number11] = (ProjDateDiff([Project Start],[Finish],"Standard")/480)

Since I want a task of either 0 or 1 day duration to show as a single date
instead of a date range, I need to compare the actual date (formatted to
exclude the time) rather than the custom number fields. This is a better
formula for the custom text field:

IIf(projdateconv([Start],pjDate_mm_dd)=projdateconv([Finish],pjDate_mm_dd),"Day " & [Number10],"Day " & [Number10] & " - Day " & [Number11])

Sorry if that messed you up... now you see why I like to break up the
formulas for ease of troubleshooting! (hopefully I won't find any other
problems this time!)

- Sue

"Sue" wrote:

> Thank you ! I may have asked the question wrong, but since you gave me both
> formulas, I got exactly what I was looking for.
>
> Here's what I did to get what I needed (for anyone else who needs a little
> more detail)
>
> I first created a custom number field with the formula John gave me:
> [Number10] = (ProjDateDiff([Project Start],[Start],"Standard")/480)+1
>
> This compares the start date to the project start date and adjusts the value
> to the number of days (which is the division by 480)
>
> Using this formula, when your projects starts on a Monday, the first task
> will be on Day 1. The next Monday will be Day 6. Working days are based on
> what is defined in the "standard" calendar.
>
> I did the same thing to compare the finish date to the project start
> [Number11] = (ProjDateDiff([Project Start],[Finish],"Standard")/480)+1
>
> Then I created a little formula to merge thetwo custom fields into a custom
> text field:
> [Text10] = IIf([Number10]=[Number11],"Day " & [Number10],"Day " &
> [Number10] & " - Day " & [Number11])
>
> So now I have a nice little timeline that looks like:
>
> Task 1 Day 1
> Task 2 Day 1 - Day 4
> Task 3 Day 4 - Day 5
> Task 4 Day 6
>
> You can substitute the formulas for [Number10] and [Number11] and get the
> same thing, but I like to break it up just to keep it simpler to troublewhoot
> if something doesn't look right.
>
> Sue
>
>
> "John" wrote:
>
> > In article <69299AD0-CCC6-471A-866F-9D9B7C863941(a)microsoft.com>,
> > Sue <Sue(a)discussions.microsoft.com> wrote:
> >
> > > Is there a way that I can format task Start and finish dates to display the
> > > number of working days from the start of a project?
> > >
> > > For example,
> > > Start: 8/9 Finish 8/11 shows as Day 1 - Day 3
> > > Start: 8/14 Finish 8/21 shows as Day 4 - Day 9
> > >
> > > I want the day numbers to take into account non-working days from the
> > > project calendar. This is different than changing the timescale in the Gantt
> > > view to Day1, Day2 because the timescale does not adjust for non-working
> > > days. I have tried using a formula in a custom field to calcuate the
> > > difference between the date and the project start date, but that method also
> > > does not account for non-working days (at least not that I could find).
> > >
> > > Any help is greatly appreciated.
> > >
> > > Make it a great day!
> >
> > Sue,
> > There are two formulas available for finding the difference between two
> > dates.
> > ProjDateDiff(date1, date2, calendar)
> > DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)
> >
> > You apparently used the first formula which does give the difference in
> > working days. What you want is the second formula. It gives the
> > difference in calendar days.
> >
> > Hope this helps.
> > John
> > Project MVP
> >
From: John on 25 Aug 2006 13:28
In article <6873FB1D-0F55-41AA-A83D-3B49135D09D6(a)microsoft.com>,
Sue <Sue(a)discussions.microsoft.com> wrote:

> Thank you ! I may have asked the question wrong, but since you gave me both
> formulas, I got exactly what I was looking for.
>
> Here's what I did to get what I needed (for anyone else who needs a little
> more detail)
>
> I first created a custom number field with the formula John gave me:
> [Number10] = (ProjDateDiff([Project Start],[Start],"Standard")/480)+1
>
> This compares the start date to the project start date and adjusts the value
> to the number of days (which is the division by 480)
>
> Using this formula, when your projects starts on a Monday, the first task
> will be on Day 1. The next Monday will be Day 6. Working days are based on
> what is defined in the "standard" calendar.
>
> I did the same thing to compare the finish date to the project start
> [Number11] = (ProjDateDiff([Project
> Start],[Finish],"Standard")/480)+1
>
> Then I created a little formula to merge thetwo custom fields into a custom
> text field:
> [Text10] = IIf([Number10]=[Number11],"Day " & [Number10],"Day " &
> [Number10] & " - Day " & [Number11])
>
> So now I have a nice little timeline that looks like:
>
> Task 1 Day 1
> Task 2 Day 1 - Day 4
> Task 3 Day 4 - Day 5
> Task 4 Day 6
>
> You can substitute the formulas for [Number10] and [Number11] and get the
> same thing, but I like to break it up just to keep it simpler to troublewhoot
> if something doesn't look right.
>
> Sue

Sue,
You're welcome and thanks for the feedback.
John
>
>
> "John" wrote:
>
> > In article <69299AD0-CCC6-471A-866F-9D9B7C863941(a)microsoft.com>,
> > Sue <Sue(a)discussions.microsoft.com> wrote:
> >
> > > Is there a way that I can format task Start and finish dates to display
> > > the
> > > number of working days from the start of a project?
> > >
> > > For example,
> > > Start: 8/9 Finish 8/11 shows as Day 1 - Day 3
> > > Start: 8/14 Finish 8/21 shows as Day 4 - Day 9
> > >
> > > I want the day numbers to take into account non-working days from the
> > > project calendar. This is different than changing the timescale in the
> > > Gantt
> > > view to Day1, Day2 because the timescale does not adjust for non-working
> > > days. I have tried using a formula in a custom field to calcuate the
> > > difference between the date and the project start date, but that method
> > > also
> > > does not account for non-working days (at least not that I could find).
> > >
> > > Any help is greatly appreciated.
> > >
> > > Make it a great day!
> >
> > Sue,
> > There are two formulas available for finding the difference between two
> > dates.
> > ProjDateDiff(date1, date2, calendar)
> > DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)
> >
> > You apparently used the first formula which does give the difference in
> > working days. What you want is the second formula. It gives the
> > difference in calendar days.
> >
> > Hope this helps.
> > John
> > Project MVP
> >
 |  Next  |  Last
Pages: 1 2
Prev: getting following error messages in project 2003
Next: Project 2003 integration with Sharepoint 3.0