How to change start1 and finish1 with duration1? RRS feed

  • Question

  • if you in project have:

    Duration, start and finish, then you can change duration by changing start and/or finish and you can change start and/or finish by changing duration.

    how can i do the same with duration1, start1 and finish1, so a change in start1 or finish1 changes duration1 and a change in duration1 changes start1 and/or finish1?



    Monday, December 9, 2013 1:38 PM

All replies

  • Hi Tony,

    In order to do that, you'll have to customize the duration 1 field writing a formula with ProjDateDiff function between start 1 and finish 1.

    But then start1 and finish1 couldn't be update from duration1, since it contains a formula.

    You might need to use duration2-start2-finish2 in addition.

    Hope this helps.

    Monday, December 9, 2013 1:48 PM
  • Hi Toni,

    I'm afraid it doesn't work quite the same way using the custom start, finish, and duration fields.  You can certainly calculate Duration1 from Start1 and Finish1 - but the formula will make Duration1 non-editable.

    ProjDateDiff(Start1, Finish1) will calculate the duration in Duration1.

    I hope this helps.


    Monday, December 9, 2013 1:50 PM
  • toni76ab,

    The only effective way to do this is with VBA and even that will require an event based macro, although it could also be done with a set of "trigger" fields (e.g. Flag1, Flag2, Flag3) that the user toggles each time an change is made to one of the three fields. The state of the trigger will tell the macro which calculation to initiate (e.g. start and/or finish from duration or duration from start and or finish)

    Of the three fields, the duration requires an extra level of analysis in the macro code to determine how the start and finish dates should be calculated. For example, if the duration is increased, should the start date be moved backward (sooner) or should the finish date be moved out (later)? The normal case would be the latter, but there may be some scenarios wherein the finish date should stay fixed, thus requiring the start date to be moved.


    Monday, December 9, 2013 4:19 PM
  • Hi John,

    i will try to make it with VBA, data will be sent to Excell and two sets of data is needed:

    task, Resource, Estimate(Hours), Price per hour, Estimated cost, start, Finish, Revised Estimate(Hours), Revised Price per hour, Revised Estimated cost, Revised start, Revised Finish, Task Done.

    therefor i need two sets of data one for the estimate and one for Revised estimate, all fields needs to be editable som therefore i need a VBA solution.i thought aboout setting a baseline so that baseline would be Estimate and Revised Estimate would be current version but i dont think it is the way.

    also having a task field for price per gives problem as resource field can hold more than one resource, but i don't think resource costfields can be shown in task gantt?



    Monday, December 9, 2013 5:29 PM
  • Toni,

    I'm curious about the "revised price per hour". On the surface that's very unusual. Once agreed upon, price isn't something that is normally subject to change.

    There is probably more to it than can be gleaned from your explanation, but the idea behind a baseline is to take a snapshot of critical project variables (e.g. start, finish, duration, work, cost, etc.) at the onset and then update the current schedule data as the plan is executed. Project even provides for up to 11 separate baselines. It's not clear why that normal Project operation won't work for you.

    Why do you need a cost value for each resource on the Gantt Chart view? That information is available on the Task Usage view, or in reverse on the Resource Usage view.


    Monday, December 9, 2013 7:36 PM
  • Hi John,

    it is a assignment i just got and i have to ask the same question, on each user there is a standard and overtime rate so maybe I can use those st for estimate and overtime for revised estimate.

    i got a table with example lines and here the same resource has different hourly rate on different tasks so i have to find out if i can use st rate and overtime rate on resources or if the task decides the rate etc.

    task1 res1 price per hour=150

    task2 res1 price per hour=200...

    The goal is to deliver data from project to Excel for creating s-curves and earned value calculations on projects.


    • Edited by toni76ab Monday, December 9, 2013 10:20 PM
    Monday, December 9, 2013 10:16 PM
  • Toni,

    Pardon me for bumping back in.  You may define differing rates for a single resource by using different rate tables.  Double click on the Resource's name to show the Resource Information form and click the Cost tab.

    Define one rate on Rate Table A and define the differing rate on Rate Table B.

    After making the assignments you may change the rate table by adding the Cost Rate Table field to the Task Usage view and switching Rate tables as needed.

    You can calculate costs automatically using the differing Rate tables.  I would not attempt to use Overtime Rates for this process.

    I hope this helps.


    Monday, December 9, 2013 11:58 PM
  • Toni,

    So you're just the "stuckee" on this. Yeah, we've all been there at one time or another.

    It's possible that the different rates were intended to be standard versus overtime but in most instances, overtime is not planned, rather it is a contingency when needed. Further, a project should not be planned with overtime included, otherwise something is wrong with the plan.

    However, another explanation for the difference in rate might be job category. A given resource may be fully capable of doing several categories of job, and, each of those job categories may be charged (i.e. priced) at a different rate. So in your example, when working task 1 the rate is indeed 150, but when working task 2 the rate is 200. However, except for overtime, the rate for that resource working task 1 should always be 150, it should not "jump" to 200. Likewise when that resource is working task 2, the rate should always be 200.

    There are two ways to handle the above. One way is to create two resources (i.e. Joe A and Joe B). Joe A's rate is 150 and Joe B's rate is 200. Depending on the task, Joe A or Joe B, is assigned as appropriate. The second way is to use one of the alternate cost rate tables (B, C D or E) for the resource. Table "A" is the default and is what shows in the Standard Rate field on the Resource Sheet. The appropriate rate table to apply to any given task is done via the Task Usage view by including the Cost Rate Table field as a column in the view and editing it at the assignment level.

    There is a whole array of earned value metrics already included in Project and I believe Project 2013 includes much better functionality for creating "S" curves than previous versions, which required export of data to Excel via visual report, VBA, or third party add-ins.


    Tuesday, December 10, 2013 12:05 AM
  • Thanks julie, this could be best the way to handle the different different rates.

    i just need to find out if hourly reate follows the resource or the task in my case.


    Tuesday, December 10, 2013 5:59 AM
  • Hi john,

    i will follow up on the questions i have to the assignment, the organisation are using both project 2010 and 2013.

    but others are already working on assingments creating s-curves in Excel my task is to deliver the data from project to Excel.


    Tuesday, December 10, 2013 6:05 AM
  • You're welcome.  Let us know how you get along.
    Tuesday, December 10, 2013 1:47 PM
  • i decided following

    that Estimate Hours (Duration), Price per hour, Estimated Cost, Start, Finish will be baselined, same values Revised will be current version so there will be x amount of datasets baseline to baselineX with early projectdata and one set with current data (Revised dataset)

    cost is set on resources A, B, C.. so different rates for each resource can be set for each task if necessary.

    Price per hour will be set on  Cost1 with this formula ([Cost]/([Duration]/60)) and price per hour is selected by selecting a, b, c... from task usage.

    but im not sure how i add Cost1 to baseline values when saving a baselines?


    Tuesday, December 10, 2013 2:04 PM
  • Hi Tony,

    Unfortunately for you, "Cost" will be copied by default in the BaselineCost, and not Cost1. A workaround would be to write a macro that will "push" cost1 in baselinecost.

    Hope this helps.

    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, December 10, 2013 2:25 PM
  • hmm,

    Yes but im also using cost having the complete cost on the task wich I also need to move to baselinecost so i have a totaly cost both in baselinecost and in cost.

    is there other baseline cost fields i can use to hold the cost per hour?


    Tuesday, December 10, 2013 2:48 PM
  • Toni,

    It looks like there is some circular logic going on here.  Project calculates costs for you based upon Work * Resource Cost.  So, I'm not sure why you are trying to calculate "Price per hour".  If you change Cost Rate Tables, it will recalculate the Cost automatically.

    Cost1 is not baselined you can, however, copy the values into another CostX field.

    Tuesday, December 10, 2013 5:18 PM
  • Im just calculate it to make it more visible for the user in tasktable.

    when adding vba code to project then i have to allow macroes every time i open, i know that by making code as addin i wont have this problem, is there a solution for that when using VBA without lowering security in Project?

    • Edited by toni76ab Wednesday, December 11, 2013 8:22 AM
    Tuesday, December 10, 2013 10:57 PM