Custom Field Formula Help RRS feed

  • Question

  • Hi,

    I'm trying to create a custom field containing a formula to record the date a task was recorded 100% complete.

    That being said, can it be done this way?

     IIf([% Complete] = 100, Now( )  

    Friday, July 6, 2018 5:41 PM

All replies

  • So this is what I ended up with in case someone wants to do something similar. This marks the date for when a project is marked 100% complete.



    I think this is working in case someone else needs this function for OTD... WHich is not recorded in Project Online standard tables.

    Friday, July 6, 2018 7:44 PM
  • Turns out that this only works when you don't do a publish all scrip as it will ick that change up and think that the task was completed that date. How do I make sure that it doesn't override with todays publish date. Should be a if "anything a date is listed in this cell" don't override command?


    Monday, July 9, 2018 5:02 PM
  • Something like this?? anyone?

    IIf([% Complete] >= 100, (IIf([Last Modified] < [Today], Now(), "Not Finished"), "Not Finished")

    Monday, July 9, 2018 8:31 PM
  • Henrik --

    The problem with your formula is that the formula will recalculate EVERY TIME you make any kind of schedule change in the project.  And you now know that the date and time captured by the formula will continue to change.

    I really cannot think of a to use a formula to capture the information you are seeking.  Perhaps others in the forum will have some ideas for you.  Hope this helps.

    Dale A. Howard [MVP]

    Tuesday, July 10, 2018 12:03 AM
  • Dale - You're absolutely right :)

    Is there a formula to not overwrite if has values?

    Wednesday, November 14, 2018 6:48 PM
  • Henrik,

    As you've seen, a custom field formula will continually re-calculate itself, so that won't work.

    Instead, here's a little vba snippet that would do the trick.  It writes the current date and time into the Date10 field for each task that a) currently has "NA" in that field, and b) has an actual finish date.  (The number represented by "2^32 - 1" is what Project actually stores in a date field when "NA" is displayed.)

    Sub DateMarkedComplete()
    Dim t As Task
        For Each t In ActiveProject.Tasks
            If Not t Is Nothing Then
                If t.Date10 >= ((2 ^ 32) - 1) And t.ActualFinish < ((2 ^ 32) - 1) Then
                        t.Date10 = Now()
                End If
            End If
        Next t
    End Sub

    As written, you'd need to explicitly execute it (typically by assigning a menu button.)  You could also make it auto-run, but that would be much more complex.

    Good luck, tom

    Wednesday, November 14, 2018 9:13 PM
  • Henrik --

    I totally agree with Tom's solution, which is using a VBA macro to make the changes capture the date when the task was marked 100% complete.  There is no way to do this with a formula, but with VBA code such as he shared with you, the job is a snap.  Hope this helps.

    And Tom, nice work, my friend!  :)

    Dale A. Howard [MVP]

    Wednesday, November 14, 2018 11:05 PM