none
Enterprise Custom Fields and Formulas (Project Server 2010) RRS feed

  • Question

  • Hi,

    I'm having some trouble doing something that I thought would be fairly straightforward: use a custom formula field to determine the number of days between the value of a date variable field and the current date.

    We have a number of projects in an "on hold" status.  I created a custom field titled "Hold Through Date" that we use as a reference point for when work is expected to begin/resume on the project in question.  I am trying to develop a field that will flag projects whose holds are expiring in the near future.  I tried using a formula that subtracts the current date from the "Hold Through Date":

    [Hold Through Date] - Now()

    But the resulting field won't populate.  What am I doing wrong?

    Thanks in advance,

    Andrew Moffitt

    Monday, June 22, 2015 6:06 PM

Answers

All replies

  • Hello,

    Try this example:

    IIf([Hold Through Date] = ProjDateValue("NA"), "Date not set", ProjDateDiff(Now(), [Hold Through Date]) / 480)

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Monday, June 22, 2015 7:09 PM
    Moderator
  • It worked!  When I refreshed the Project Center, the custom field was still blank, but I decided to re-publish one project on a whim; when I did so, a result appeared in the custom field for that project!  Thank you!

    Just one minor detail: the result it gave me was 137.5 days.  I didn't expect a non-integer result, but that makes sense given the division in the formula.  What's the best way to round that result to the nearest integer?

    Also, since I'm a bit of a newbie to Project Server, can you explain what the "ProjDateValue("NA")" portion of the formula does?  It looks like it's designed to validate that a date exists, but I don't know what variable that refers to, or what the "NA" means.

    Thanks again!

    Andrew



    Monday, June 22, 2015 7:34 PM
  • What about this:

    IIf([Hold Through Date] = ProjDateValue("NA"), "Date not set", Round(ProjDateDiff(Now(), [Hold Through Date]) / 480))

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Monday, June 22, 2015 7:44 PM
    Moderator
  • Looks like that did the trick.  Thank you very much!!!

    Andrew

    Monday, June 22, 2015 7:48 PM
  • Okay, so now that I created the formula field and verified that it populated data for our projects, it looks like it doesn't automatically re-calculate each day until I either modify and save or re-publish each project.

    I found your post on writing a powershell script that will automatically publish my entire portfolio either as-needed or on a scheduled basis, and I successfully configured and ran the powershell script.  But it doesn't seem to have helped; the projects which displayed 5 days of Hold time remaining yesterday still display that now, while the ones I manually edited and re-published display 4 days.

    Can you please offer some help here as well?

    Thank you,

    Andrew

    Tuesday, June 23, 2015 9:57 PM
  • Hello. For the project formulas to calculate you need to open the project recalcute then save and publish. To get a time sensitive value you might need to look at reporting, SSRS etc that will calculate on loading. Paul

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, June 23, 2015 10:30 PM
    Moderator
  • Thanks, I appreciate the response.  I'll check out a reporting solution.

    Andrew

    Wednesday, June 24, 2015 12:04 AM