none
Can Timescale Data be automatically balanced to Resources' total availability? RRS feed

  • Question

  •  Im using standard MS Project 2010 for Resource Planning (forecasting). My understanding of Projects VBA is on the lowerside of moderate but i dont even know where to start to acheive this...What Id like to know is, if there is a way to adjust a Resources' TimeScaled Data on a specific task to be the Balance of the other assigned Tasks, so the Resources Total Hours for that week is his max availability? I don't know if that makes sense, so heres an example:

    If Bob is working on 2 Tasks (total 30hrs/wk) but when he is not doing that he does "Office Duties" (currently 5hrs/wk). note: the hours on the two tasks are contoured and will vary from week to week, so changing his assignment units to 25% wont work.

    Thats only 35hrs/wk, but he's available for 40hrs/wk.

    Is there a macro that can work out the total of Bob's 2 Tasks (30) from his total availible hours (40) and adjust the "Office Duties" hours to the Balance (10hrs) for each week of the office duties Duration (but shows "0" in lieu of negatives).

    Resource Usage    w1    w2    w3    w4    w5

    Bob                     35     32    35     43    47

      Task1                12     10      5     10    20

      Task2                18     15     25    25    22

      Office Duties        5      7       5      8      5

    *Office Duties --> 10     15     10     5     "0" (not -2)

    Because this wont apply to all the resources Im guessing i'll need to select the assignment "Office Duties" under Bobs name then execute the macro in Resource Usage View, which Im happy to do.

    Is this even possible? Thanks for any insight you can provide.

    Wednesday, September 17, 2014 5:52 AM

Answers

  • Fatt Matt,

    I'm in agreement with Rod, you will find this a lot easier and less frustrating if you do it in Excel. You want to keep it simple, that will keep it simple. Project is an application for creating and managing schedules and that's not what you are doing, so our "greater minds", (Rod's is greater, mine is lesser), are trying to assist you by pointing you in the best direction.

    However, that being said, I am a hopeless mark for a VBA challenge so tell you what I"ll do. I'll complete and post the code I started for your two task example above. Re-structuring it to work with your 150 project, 900 resource file would be a major undertaking considering the variables I outlined previously and others I haven't thought of yet. Could I do the necessary re-write? Sure. Will I do it? No.

    Please note that if your "resource managing" were done in Excel, the macro below could probably be done with a single formula. Just saying.

    If this answers your original post, and it definitely does, then please mark it as the answer.

    John

    Option Compare Text
    'This macro will analyze a specific resource's main assignment tasking and adjust ancilliary
    '   hours (in this example called "Office duties") such that total work hours for the week
    '   will be 40 hours
    'It assumes an auto-scheduled plan and a standard calendar
    'Written by John 9/28/14
    Sub AdjustOffDut()
    Dim t As Task
    Dim r As Resource
    Dim a As Assignment
    Dim TSWkMin As TimeScaleValues
    Dim WkMin As TimeScaleValue
    Dim i As Integer, MPD As Integer
    Dim EDate As Date, LDate As Date, WkFi As Date, St As Date, Fi As Date
    Dim TWkMin As Single, AvailWkMin As Single, OffDut As Single

    EDate = ActiveProject.ProjectSummaryTask.Start
    LDate = ActiveProject.ProjectSummaryTask.Finish
    MPD = ActiveProject.HoursPerDay * 60
    For Each r In ActiveProject.Resources
        St = Application.DateSubtract(LDate, MPD): Fi = Application.DateAdd(EDate, MPD)
        If r.Name = "bob" Then
            i = 1
            'find earliest and latest date of all assignments for this resource
            For Each a In r.Assignments
                If a.Start < St Then St = a.Start
                If a.Finish > Fi Then Fi = a.Finish
                WkFi = St
            Next a
            'now go week by week for all assignments
            While WkFi < Fi
                For Each a In r.Assignments
                    'adjust end of each timescale period to be Friday at 5:00 pm
                    WkFi = Application.DateAdd(DateAdd("d", 6 - Weekday(St), St), MPD)
                    If WkFi > Fi Then WkFi = Fi
                    Set TSWkMin = a.TimeScaleData(St, WkFi, pjAssignmentTimescaledWork, _
                        pjTimescaleWeeks, 1)
                    If a.TaskName = "Office duties" Then
                        AssIndx = i
                    Else
                        For Each WkMin In TSWkMin
                            If WkMin = "" Then
                                WkMin = 0
                            Else
                                TWkMin = TWkMin + WkMin
                            End If
                        Next WkMin
                    End If
                    i = i + 1
                Next a
                'find total available minutes in this week's period
                AvailWkMin = Application.DateDifference(St, WkFi)
                OffDut = 0
                If TWkMin < AvailWkMin Then OffDut = AvailWkMin - TWkMin
                r.Assignments(AssIndx).TimeScaleData(St, WkFi, pjAssignmentTimescaledWork, _
                    pjTimescaleWeeks, 1).Item(1).Value = OffDut
                'reset starting point to Monday of next week and total minutes for week
                St = DateAdd("h", 63, WkFi)
                TWkMin = 0: i = 1
            Wend
        End If
    Next r
                    
    End Sub

    • Edited by John - Project Sunday, September 28, 2014 9:12 PM da code
    • Marked as answer by Fatt Matt Tuesday, September 30, 2014 2:59 AM
    Sunday, September 28, 2014 3:25 PM

All replies

  • Yes this is possible using VBA, but the rules around what the VBA code should do are not!

    For example, lets say Bob has two tasks running simultaneously. Where should the macro add any "spare" hours? To both or just one? Now multiply this by hundreds of tasks and resources. Now lets say the Office duties have to be increased for a week or two. Which assignment should have its hours reduced?

    The choices for this will vary by project and over time for each project. As such I suggest that it isn't worth doing. I would calculate the average weekly hours for office duties and subtract this from Max units and only assign the average available hours. Some weeks you will lose, some you will gain.

    If Office hours average 8h/w then max units = 32h in a 40h week = 80%


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, September 18, 2014 8:55 PM
    Moderator
  • Thanks Rod, in Bob's Resource Usage Table in my above post

    the " *Office Duties --> 10     15     10     5     "0" (not -2) " line was supposed to be the result of the macro execution. The three lines above that are what Bob is assigned to; "Task1, Task2 & Office Duties"...3 assignments altogether.  sorry I didn't make that clearer.

    Only the Office Duties Timephased Data would be required to be Recalculated, "Task1" & "Task2" are Data updates from Project managers and should't be affected by the office duties recalculation. So Office Duties are a "top up" activity, for example: sweeping floors, this would not take priority over actual projects and therefore office duties would only ever be the balance of Bob's utilisation.

    so it is possible?

    Fatt Matt

    Tuesday, September 23, 2014 10:01 AM
  • Fatt Matt,

    As Rod said, virtually anything is possible with VBA but investing the effort to develop a macro to do something isn't always worth it in the end.

    You mentioned in your initial post that office duty re-assignment won't apply to all resources. Which resources and how are they determined? Are your tasks fixed duration, fixed work or fixed units? Does any given resource have more than two task assignments in addition to office duties? Are the task assignments always of equal duration or for example, may task 1 be 5 weeks and task 2 be 7 weeks duration? Do all task assignments always start at the same time or for example, may task 1 start on w1 and task t start on w2? Likewise, do all tasks start on a Monday?

    Obviously there's lot of stuff to consider.

    John


    • Edited by John - Project Tuesday, September 23, 2014 11:44 PM update
    Tuesday, September 23, 2014 4:25 PM
  • ah, ok...I see what you're saying. i knew The macro would be complex but had not thought it would need to check those conditions you mentioned John. I have close to 900 resources but only about 200 are assigned to Office Duties, some of which could could be solely assigned to Office Duties but most could be anywhere up to 25 other projects of varied start & finish dates, Durations, etc. The only things that are constant are all tasks are fixed units & manually scheduled, everything else is variable. I was hoping it was as straight forward as selecting the Office duties task of the resource while in Resource usage view and running a macro like:

    "for each week in office duties duration

    sum the total utilisation of the active resources assignments for that week - office duties for that week

    if the sum is => 40 then office duties for that week = 0

    else office duties for that week = 40-the sum

    next week"

    then manually rinse and repeat for another Resource. But I guess it's not that simple, I just wasn't sure how to begin. 

    Thankyou Rod & John for your wisdom , the macro might not be worth it in the end but it was worth asking the question.

    cheers

    Wednesday, September 24, 2014 2:28 AM
  • Wow, hold your horses. All tasks are manual and you have 900 resources? Totally pointless doing anything extra until you have all tasks auto scheduled, linked and if the project is time critical a valid critical path achieved!

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, September 24, 2014 8:39 AM
    Moderator
  • Fatt Matt,

    For the simple scenario shown in your initial post, the macro would not have been very complex. Matter of fact, I started coding something but then stopped as I mulled over the various issues I brought up. Now that you've elaborated more on the total magnitude of your project, indeed the code would be moderately to very complex. And the real kicker is the manually scheduled aspect. I personally don't have any use for manual scheduling but I can see where it might be useful in lieu of the old whiteboard approach to conceptual project planning, prior to adding resources.

    Now that you've been re-calibrated, let me pose this solution. Are "office duties" really a viable task? I mean is it something you need to plan and can you realistically show progress against? If anything it sounds more like what is called level-of-effort tasking which is measured by the passage of time rather than via actual hours of work. But why even mess with it, instead use Rod's suggestion and adjust your maximum unit availability to 80%.

    John

    Wednesday, September 24, 2014 3:52 PM
  • To Rod,

    I know it sounds pointless to manually Schedule in such a powerful tool such as MS Project. I mentioned in my original post that I was using MS Project for Resource Planning (as opposed to Project Planning) so its Resource Driven with Resource information/utilisation and less about % complete etc. It does feel like its completely back to front to the normal application of this software. There's very few (if any) forum threads that relate to this backward type of Planning. I have over 150 individual projects (not tasks within a project but actual Projects) so it would be impossible to follow the usual finish to start succession let alone a Critical Path (thats the project managers Job :) ). I discovered that the constraints and start & finish dates were re-calculating when Auto Schedule was active, i switched it off and haven't had a problem since. All my inputs are in the form of resource allocations and need to be updated manually, and the resources do chop and change projects as required, so the projects needed to be more ridgid, like a platform, to load the resources onto without it affecting the project constraints or the other resources timephase data. It was hard to get my head around when I was told to create this Resource Plan for corporate reporting. I've no experience in Project Planning and I'm still feeling my way around MS Project after being thrown in the deep end and perhaps I'm not going about this the right way but it works flawlessly.

    To John,

    In my example I called it Office Duties but its acually Corporate Support (eg Office admin, HR , Finance, me), I was just trying to keep it simple. to answer your question, I would prefer not to have to change the Resources "Office Duties" hours week by week or cell by cell at all but many inputs I receive from Dept Managers ask me to "balance Office Duties to 40h" so a macro would save me a great deal of time. I didn't know where start so I posted here hoping a greater mind that my own could assist me in building a simple frame work that I could adapt. Half the fun is getting it working :)

    I would love to see what you were working on John even in its simplest form... it may just give me a prod in the right direction.

    Thanks for your time Guys

    Friday, September 26, 2014 9:55 AM
  • By having everything manually scheduled, you do the equivalent in Excel of using a calculator to sum some numbers then typing the total in instead of using a formula. With that many projects and resources, some must follow others. At the moment if there is a change, you have to manually re-calculate and update everything. With auto calculation and linking of what tasks and projects you can link, your work load is a lot less.

    As you have it at the moment, you are probably better off using Excel given your lack of experience with Project.

    For that number of projects and resources you need something like Project Online or Project Server so all PMs can publish their schedules to the database from where it is easy to read the hours per week for each resource. This can be reported on via the web or Excel.

    PMs can also use Resource Plans to forecast ahead what resources they need on a FTE basis. So most of your work would be done for you.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Saturday, September 27, 2014 10:37 PM
    Moderator
  • Fatt Matt,

    I'm in agreement with Rod, you will find this a lot easier and less frustrating if you do it in Excel. You want to keep it simple, that will keep it simple. Project is an application for creating and managing schedules and that's not what you are doing, so our "greater minds", (Rod's is greater, mine is lesser), are trying to assist you by pointing you in the best direction.

    However, that being said, I am a hopeless mark for a VBA challenge so tell you what I"ll do. I'll complete and post the code I started for your two task example above. Re-structuring it to work with your 150 project, 900 resource file would be a major undertaking considering the variables I outlined previously and others I haven't thought of yet. Could I do the necessary re-write? Sure. Will I do it? No.

    Please note that if your "resource managing" were done in Excel, the macro below could probably be done with a single formula. Just saying.

    If this answers your original post, and it definitely does, then please mark it as the answer.

    John

    Option Compare Text
    'This macro will analyze a specific resource's main assignment tasking and adjust ancilliary
    '   hours (in this example called "Office duties") such that total work hours for the week
    '   will be 40 hours
    'It assumes an auto-scheduled plan and a standard calendar
    'Written by John 9/28/14
    Sub AdjustOffDut()
    Dim t As Task
    Dim r As Resource
    Dim a As Assignment
    Dim TSWkMin As TimeScaleValues
    Dim WkMin As TimeScaleValue
    Dim i As Integer, MPD As Integer
    Dim EDate As Date, LDate As Date, WkFi As Date, St As Date, Fi As Date
    Dim TWkMin As Single, AvailWkMin As Single, OffDut As Single

    EDate = ActiveProject.ProjectSummaryTask.Start
    LDate = ActiveProject.ProjectSummaryTask.Finish
    MPD = ActiveProject.HoursPerDay * 60
    For Each r In ActiveProject.Resources
        St = Application.DateSubtract(LDate, MPD): Fi = Application.DateAdd(EDate, MPD)
        If r.Name = "bob" Then
            i = 1
            'find earliest and latest date of all assignments for this resource
            For Each a In r.Assignments
                If a.Start < St Then St = a.Start
                If a.Finish > Fi Then Fi = a.Finish
                WkFi = St
            Next a
            'now go week by week for all assignments
            While WkFi < Fi
                For Each a In r.Assignments
                    'adjust end of each timescale period to be Friday at 5:00 pm
                    WkFi = Application.DateAdd(DateAdd("d", 6 - Weekday(St), St), MPD)
                    If WkFi > Fi Then WkFi = Fi
                    Set TSWkMin = a.TimeScaleData(St, WkFi, pjAssignmentTimescaledWork, _
                        pjTimescaleWeeks, 1)
                    If a.TaskName = "Office duties" Then
                        AssIndx = i
                    Else
                        For Each WkMin In TSWkMin
                            If WkMin = "" Then
                                WkMin = 0
                            Else
                                TWkMin = TWkMin + WkMin
                            End If
                        Next WkMin
                    End If
                    i = i + 1
                Next a
                'find total available minutes in this week's period
                AvailWkMin = Application.DateDifference(St, WkFi)
                OffDut = 0
                If TWkMin < AvailWkMin Then OffDut = AvailWkMin - TWkMin
                r.Assignments(AssIndx).TimeScaleData(St, WkFi, pjAssignmentTimescaledWork, _
                    pjTimescaleWeeks, 1).Item(1).Value = OffDut
                'reset starting point to Monday of next week and total minutes for week
                St = DateAdd("h", 63, WkFi)
                TWkMin = 0: i = 1
            Wend
        End If
    Next r
                    
    End Sub

    • Edited by John - Project Sunday, September 28, 2014 9:12 PM da code
    • Marked as answer by Fatt Matt Tuesday, September 30, 2014 2:59 AM
    Sunday, September 28, 2014 3:25 PM
  • Thanks Guys, I've heeded your advice and switched it Auto Schedule, and ran the macro after switching the Example names and Tasks and it worked perfectly! I can play around with this to work on the selected Resource & Assignment. Thanks for your patience

    Tuesday, September 30, 2014 2:59 AM
  • Fatt Matt,

    You're welcome and thanks for the feedback. I'm glad you saw the light about auto schedule mode.

    John

    Tuesday, September 30, 2014 3:57 PM