Automate Status Date in MS Project RRS feed

  • Question

  • Hi 

    I am using MS project online and Professional; I have 19 project plans for which every alternate Thursday is the Status reporting date. Rather than going in every 19 projects and changing the status date (project level) field; is there a way to automate it by creating a custom field (using formula) that will choose or display every alternate Thursday from today in a column? (If Yes - Please help) or Is there a way to can have a global status date field that will be reflected to all my project plans at one step? (If Yes - Please can someone guide me?)

    If you have any other idea for doing it please let me know. I would appreciate any feedback's

    (Except Macro and VB Scripting)

    Thank you in advance.


    • Edited by RohanMate Monday, August 20, 2018 7:20 PM
    Monday, August 20, 2018 7:16 PM

All replies

  • RM --

    To the best of my knowledge, I do not believe there is a way to automate setting the Status Date except through VBA.  Regardless of how you set the Status Date, you will still need to open all 19 projects in the process.  Hope this helps.

    Dale A. Howard [MVP]

    Monday, August 20, 2018 8:16 PM
  • Hi Dale,

    Thank you for reverting back.

    Is there a formula that will display alternating Thursday (Date) in a column?

    For example: If this week Thursday passes it will automatically show alternate week's Thursday date?

    Thank You,


    • Edited by RohanMate Monday, August 20, 2018 8:25 PM
    Monday, August 20, 2018 8:24 PM
  • RohanMate,

    The following formula will give a date that is 2 calendar weeks from the current Status Date, which if currently set as a Thursday, will give a date 2 weeks later.

    datevalue([Status Date]+14)

    But, that won't do much good because it still doesn't update the Status Date itself, it simply gives you a custom field date that you could just as easily see on your wall calendar.

    Dale is right, you kinda killed it when you said "(except macro and VB scripting)". Now that would make it easy to get what you want. It could be written as an Open Event macro that fires each time the file is open such that it would detect if the current status date is still valid or if it needs to be updated.


    Monday, August 20, 2018 9:38 PM
  • This VBA will loop though the MSP files in a folder, and do something with each one and save it and close it. Make the obvious necessary edits.

    Sub Loop_Files()
    ' Macro Loop_Files
    ' Macro 22/07/18 21:28 by Trevor Rabey.

    Dim FolderPath As String 'store the folderpath in memory.
    Dim FileName As String 'store the file name in memory.

    'error handling. If there is an error the macro will go to the bottom of the macro where the error name is
    'On Error GoTo ErrHandler

    'speed up macro.
    'True or False allows or suppresses updating screen while macro runs.
    Application.ScreenUpdating = False
    'True or False allows or suppresses showing dialog boxes while macro runs.
    Application.DisplayAlerts = False

    'Call Dir the first time, pointing it to all Microsoft Project files in the folder path.
    FolderPath = "X:\PROJECTS\PLANNING\Perfect Project Planning\"
    'Check to make sure that the right most character is a backslash
    If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath + "\"

    FileName = Dir(FolderPath & "*.mpp")

    ' Loop until Dir returns an empty string.
    Do While FileName <> ""

        FileOpenEx Name:=FileName, ReadOnly:=False, FormatID:="MSProject.MPP"

    'Put whatever you want to do here.
    'BaselineSave All:=True, Copy:=0, Into:=0
    'BaselineClear All:=True, From:=0
    OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevel1
    ProjectSummaryInfo StatusDate:="17/08/18 12:00"


    ' Use Dir() to get the next file name.
    FileName = Dir()
    'end of loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub

    Tuesday, August 21, 2018 9:52 AM