none
Compare finish date with target date RRS feed

  • Question

  • Would like to keep track of late Projects by comparing the finish date with the target date but are unable to do so. The idea is just to filter and count the summary tasks which have a later Finish date than the target date and present that as a the number of late Projects. However it does not work, when trying to setup the filter Project tells that the target value can not be used for comparasion against the finish date.

    Does anyone have a workaround or maybe a better solution to keep track of late Projects. Please note that we are talking about Projects that risk to be late compared to a date promised to a customer. When we plan we always add some extra time at the end to have some extra when the Project is delayed at a certain department, however we want to keep track of how many late Projects we have and how late they are. Are not after seeing the status for each departments, only how we are doing in relation to what we have promised our customers.

    Kind regards //Anders

    Monday, August 25, 2014 10:20 AM

Answers

  • Pardon me for bumping in with a slightly different approach.  i understand you want to count the number of tasks which are later than a "target date".

    I'm assuming that you have used a spare date field (Date1) for the target date.  Add a spare Number field (Number1) to the table and customize the field with the follow formula:

    IIf(Date1>Finish,0,1)

    The field will show 1 if the task finish is later than the "target date" you've entered into Date1.

    In the Custom Fields dialog, select "Sum" in the "Calculation for task and group summary rows."  That should give you a count of the number of tasks that are later than date.

    I don't have 2013 in front of me, but I believe you can use the Number1 field in a text box in a report.

    • Marked as answer by AT_lamnea Thursday, August 28, 2014 9:13 PM
    Wednesday, August 27, 2014 10:39 AM
    Moderator
  • AT_lamnea:

    Use the method that Julie suggested to calculate the total number in a custom number field, then you can use the method shown in the following video to place the total value in the graphical report. 

    http://www.youtube.com/watch?v=YtuTYoELwM8

    This article also shows how to count items: http://www.mpug.com/articles/ask-the-experts-counting-task-occurrences-with-microsoft-project/
    • Edited by Ismet Kocaman Wednesday, August 27, 2014 4:01 PM Added article link
    • Marked as answer by AT_lamnea Thursday, August 28, 2014 9:13 PM
    Wednesday, August 27, 2014 3:59 PM
  • Have I'm guessing you have entered the formula above into a Number field?  The default value for a number field is zero, so there isn't a way to remove that default value.  You can enter your formula into a Text field and replace the 0 with " " - the issue is you cannot use the Sum or Count in a Text field.

    • Marked as answer by AT_lamnea Friday, August 29, 2014 5:31 AM
    Thursday, August 28, 2014 11:17 PM
    Moderator
  • This might not be a feasible solution (!): use the custom number field (e.g. Number1) to calculate and roll up the values, and then use a custom text field with the formula iif ( [Number1] = 0, "", [Number1] ) and select "Use formula" in the section "Calculation for task and group summary rows". Then you can hide the column of the custom number field. 



    • Edited by Ismet Kocaman Friday, August 29, 2014 1:32 AM
    • Marked as answer by AT_lamnea Friday, August 29, 2014 5:36 AM
    Friday, August 29, 2014 1:21 AM

All replies

  • When I have promised finish date, I like to keep that event and date in view so I just name the event as a milestone task (zero duration) near the top of the list, and give it a Must Finish On date constraint. Of course it just sits there by itself not linked to anything as predecessor or successor. I also always have a project finish milestone which is always the latest task and the final successor of all of the tasks, either directly or indirectly. This finish milestone is usually at the bottom of the list but can also be at the top of the list near my promised finish date milestone, so easy and obvious when the two are compared.

    You could also try putting a deadline on the finish milestone (but I don't like them).

    Monday, August 25, 2014 10:44 AM
  • Thank you very  much for your reply Trevor!

    We would like to work without milestones if possible. Have been working with the target date on the toplevel summary items, this works just fine (so far).

    What really is the target date supposed to be used for ?

    Maybe explained a bit poor.The goal is to find a way to keep track of how many orders we currently are late with compared to the dates promised to our customers (if we stick to the current plan.) We have about 100 summary tasks and we want to see how many of them we are late with. Is there a good way of doing this?

    Kind regards //Anders

    Monday, August 25, 2014 11:09 AM
  • I just know Julie will answer this probably better than me, but how about making a plan which has a finish date earlier than or no later than your promised finish date, and baselining. Then if you use a view which shows both the current schedule and the baseline, such as the tracking Gantt view, you see both bars. You also have a Status field/column. Then you can run the late tasks filter, which filters for tasks which have status = late.

    When you promise a delivery date, is this based on first finding out how long the project will take given the durations estimates and the predecessor/successors, or do you promise a date first, and then find out when you make the plan afterwards that it was not feasible or was over-ambitious?

    Monday, August 25, 2014 1:21 PM
  • Anders --

    Here is what I would recommend you do so that you do not need to create any kind of custom solutions to your problem:

    1. Completely plan the project in question.
    2. Save a Baseline for the project to capture the original Duration, Start, Finish, Work, and Cost for every task, resource, and assignment.
    3. Manually enter progress in the project, especially using the Actual Start date and Actual Finish date fields, if at all possible.
    4. Analyze schedule variance using the Tracking Gantt view and the Variance table.  The Variance table will show you one column of great interest to you, the Finish Variance column, which will show you Finish date slippage.

    Hope this helps.


    Dale A. Howard [MVP]

    • Proposed as answer by John - Project Monday, August 25, 2014 2:04 PM
    Monday, August 25, 2014 2:01 PM
    Moderator
  • That is for sure a possibility, the tricky part is that we want it on a report as a value, stating the number of late orders (optionally with a total number of days delay for all orders) As per example below:

    Number of late orders: 6  (15 days of total delay)

    It depends wheter the customer demands a certain date or if we can set it, so it can be either of them. Normally we tell the customer a date we can keep and share the time available between the various activities.

    Kind regards //Anders

    Monday, August 25, 2014 2:13 PM
  • Dale, thanks for introducing the Variance table, that was a good one.

    But why can it not be as simple as just taking the finish date and compare it with the target date? We are entering the date of shipment into the target date field and then we get a red symbol to indicate if an order is late. Really it would just be to Count the red status marks to get to know the number of late orders to get the information, but the intention is to get it shown in a report as a simple value.

    Kind regards //Anders

    Monday, August 25, 2014 2:32 PM
  • Anders --

    Another solution:

    1. On tasks that have a target date, double-click the task, select the Advanced tab in the Task Information dialog, enter the target date in the Deadline field, and then click the OK button.
    2. Right-click on the Finish column header and select the Insert Column item on the shortcut menu.
    3. In the list of available columns, select the Deadline column.  This column represents the target date.
    4. Enter progress against tasks, especially entering Actual Start and Actual Finish dates where possible.

    When a task Finish date slips past the Deadline date for the task (the task is going to miss the target date), Microsoft Project displays a red diamond with a white exclamation point in the Indicators column, indicating that the Finish date is later than the Deadline date.  To see at a glance all tasks with Deadline dates, so you can ascertain which ones have missed Deadline dates, apply the Tasks with Deadlines filter.  Alternately, you could also create your own custom filter to show you tasks with missed Deadline dates.  The criteria in the Filter would be Finish > Deadline.

    Hope this helps.


    Dale A. Howard [MVP]

    Monday, August 25, 2014 3:31 PM
    Moderator
  • Anders,

    Trevor and Dale have tried to steer you towards the normal tracking fields and methods already available in Project, but let me take a more direct approach to your exact question. First of all, you didn't tell us what field you are using as the "target date". I'll assume it is the Date1 field manually entered for each summary line.

    You could probably produce something close to the report you want with custom field formulas, filters and some manual counting but let me offer a more automated approach. The following macro will examine each summary task in your project and determine if the finish date is before or on the target date (Date1), or if it is after the target date. If it is after the target date, that will count as one late project/order and add it to a running count of late orders. The difference in days will also be tracked and added to a running total of delay. At the end, a message will appear in the exact format you describe.

    Sub LateReport()
    Dim t As Task
    Dim LateCount As Integer
    Dim LateDays As Integer
    Dim TotDelay As Integer
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Summary = True Then
                If DateValue(t.Finish) > DateValue(t.Date1) Then
                    LateCount = LateCount + 1
                    LateDays = DateValue(t.Finish) - DateValue(t.Date1)
                    TotDelay = TotDelay + LateDays
                End If
            End If
        End If
    Next t
    MsgBox "Number of late orders: " & LateCount & " (" & TotDelay & " days of total delay)"
    End Sub

    Note, in my view, and I'm sure in Trevor and Dale's also, the Finish field of the summary line does not indicate completion, it simply indicates the scheduled finish date of the last task in the subtasks under that summary. I think you should use the Actual Finish date as the comparison value since it indicates when the project/order is complete. If you do decide, actual finish is a better choice, then substitute t.ActualFinish for t.Finish in the above macro.

    My input.

    John

    Tuesday, August 26, 2014 2:30 AM
  • Excellent John, this is as near as it can be. Perfect! Just remains to figure out a way to get it into the report. Can now see the date in the Messagebox. Do you have any experience on how to get it shown on a report? Have now made a filter which sorts out thes items so they can be viewed in the Gantt chart view, however are still trying to get the values into the report using a table instead of a messagebox.

    Thanks!

    Kind regards //Anders

     
    Tuesday, August 26, 2014 6:50 AM
  • Anders,

    Define what you mean by "report". If you are looking to have this information appear in one of Project's built-in or custom reports, then I'm afraid that won't happen. If you simply mean that you want something you can print or sent to someone else, then that is certainly possible.

    If you want something that you can see within Project, then the macro can be modified to put the same data shown by the user message in a text field for the Project Summary Task. If you need/want a separate report that can be sent out, (e.g. an Excel report that others can view/print even though they may not have Project), then the macro could be modified to do that.

    It would help if you could show us a mockup of the report you want and attach a screenshot.

    John

    As a point of reference, those of us who respond on these forum are volunteers who give of our time to help users with questions. If you find any or all of the responses to be helpful, we appreciate a vote or a mark as answer. Doing so will not stop the discussion nor close the thread.

    Tuesday, August 26, 2014 2:32 PM
  • Below is an image of how the report is presented.  The yellow field should contain the number of late orders and the total delay. As you say it seems difficult to get it there. Perhaps it is possible to do with VBA programming.

    Will for sure mark as helpful and as answer

    Wednesday, August 27, 2014 7:09 AM
  • Pardon me for bumping in with a slightly different approach.  i understand you want to count the number of tasks which are later than a "target date".

    I'm assuming that you have used a spare date field (Date1) for the target date.  Add a spare Number field (Number1) to the table and customize the field with the follow formula:

    IIf(Date1>Finish,0,1)

    The field will show 1 if the task finish is later than the "target date" you've entered into Date1.

    In the Custom Fields dialog, select "Sum" in the "Calculation for task and group summary rows."  That should give you a count of the number of tasks that are later than date.

    I don't have 2013 in front of me, but I believe you can use the Number1 field in a text box in a report.

    • Marked as answer by AT_lamnea Thursday, August 28, 2014 9:13 PM
    Wednesday, August 27, 2014 10:39 AM
    Moderator
  • Are using: IIf([Text3]<>"";DateDiff("d";[Slut];[Text3]);0) to the the number of Days late for each summary task (really to not want to have the "0" values for each acitivity but have not been able to get the field blank). So these are the values to be counted / summarized. Did try to find the field in a textbox in the report, but have so far been unable to do so.

    See below image, -6 indicates an order which is currently scheduled as 6 Days late.

    • Proposed as answer by Ismet Kocaman Wednesday, August 27, 2014 4:13 PM
    • Unproposed as answer by Ismet Kocaman Wednesday, August 27, 2014 4:13 PM
    Wednesday, August 27, 2014 11:13 AM
  • Anders,

    Sorry, I can't help you with the expanded report functionality in Project 2013 as I don't have that version. I'll let Julie take it from here.

    John

    Wednesday, August 27, 2014 3:23 PM
  • AT_lamnea:

    Use the method that Julie suggested to calculate the total number in a custom number field, then you can use the method shown in the following video to place the total value in the graphical report. 

    http://www.youtube.com/watch?v=YtuTYoELwM8

    This article also shows how to count items: http://www.mpug.com/articles/ask-the-experts-counting-task-occurrences-with-microsoft-project/
    • Edited by Ismet Kocaman Wednesday, August 27, 2014 4:01 PM Added article link
    • Marked as answer by AT_lamnea Thursday, August 28, 2014 9:13 PM
    Wednesday, August 27, 2014 3:59 PM
  • Are using: IIf([Text3]<>"";DateDiff("d";[Slut];[Text3]);0) to the the number of Days late for each summary task (really to not want to have the "0" values for each acitivity but have not been able to get the field blank). So these are the values to be counted / summarized. Did try to find the field in a textbox in the report, but have so far been unable to do so.

    See below image, -6 indicates an order which is currently scheduled as 6 Days late.

    There is no picture shown here. 
    Wednesday, August 27, 2014 4:15 PM
  • Julie, do you perhaps know how to get rid of the zero values in the cells which is created using this formula, Have tried to replace the "0" at the end of the formula but then the formula does not work.

    IIf([Text3]<>"";DateDiff("d";[Slut];[Text3]);0)

    Kind regards //Anders

    Thursday, August 28, 2014 9:05 PM
  • No that is correct, an error occured while posting. Still the answers were enough to solve the problem, thanks to everyone who spent time assisting with valuable information. Did ask Julie one addtional question about how to get rid of the "0" values. Otherwise the question is answered.

    Kind regards //Anders

    Thursday, August 28, 2014 9:11 PM
  • Thanks anyway John, Julie assisted in the best possible way.

    Kind regards //Anders

    Thursday, August 28, 2014 9:13 PM
  • Have I'm guessing you have entered the formula above into a Number field?  The default value for a number field is zero, so there isn't a way to remove that default value.  You can enter your formula into a Text field and replace the 0 with " " - the issue is you cannot use the Sum or Count in a Text field.

    • Marked as answer by AT_lamnea Friday, August 29, 2014 5:31 AM
    Thursday, August 28, 2014 11:17 PM
    Moderator
  • This might not be a feasible solution (!): use the custom number field (e.g. Number1) to calculate and roll up the values, and then use a custom text field with the formula iif ( [Number1] = 0, "", [Number1] ) and select "Use formula" in the section "Calculation for task and group summary rows". Then you can hide the column of the custom number field. 



    • Edited by Ismet Kocaman Friday, August 29, 2014 1:32 AM
    • Marked as answer by AT_lamnea Friday, August 29, 2014 5:36 AM
    Friday, August 29, 2014 1:21 AM
  • Julie,

    Almost thought that it had to do with that, thanks for confirming. Do want to be able to sum and count so will continue to use it, but got a possible workaround by Ismet, will try to see if it works.

    Kind regards //Anders

    Friday, August 29, 2014 5:36 AM
  • Ismet, this is a possible alternative to use, it works fine, and the info becomes more clear when all the Zero values dissapear.

    Thanks.

    Kind regards //Anders

    Friday, August 29, 2014 5:39 AM