Display of Start and Finish dates

[Sue]

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!

 

[John]

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

 

[Sue]

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]

*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

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,
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

 

[John]

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]

*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),"Da
y " & [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,
I'm not a big fan of complex formulas for the same reason you cite.
Usually I go directly to VBA but breaking up a complex formula by using
multiple custom fields is a good choice also.

John

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

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