MS Project - True day rate charge for accurate cost projection/tracking RRS feed

  • Question

  • My issue is that of achieving accurate costing. I need to schedule a resource (equipment and people) that are charged at a day rate, however each resource may be assigned numerous tasks in a single day. Regardless of whether the resource works a single hour or is utilised for the whole day the same rate applies. The issue that I have is I cannot get MS Project (2013) to 1) apply a single unit of the day rate total if there are ranging hours worked in a single day, and 2) achieving 1) while having several separate tasks in a single day for the one resource i.e. it would ideal to have the day rate applied the once at the initial task for the day.

    By way of example: The Resource is John. He will work on Task 1, and Task 2 at March 1st, and Task 3 and Task 4 at March 2nd. John will be within the nominal 8 hour working day – as per default in MS Project – however tasks 1 & 2 are two hours while tasks 3 & 4 are three hours each and a single day rate is charged irrespective of working a ‘full’ (eight hour day).

    Ideally, I would like to set-up the schedule so that I could easily alter some duration and the respective changes (flowing into the next day/week/hitch etc), occur automatically and correctly, as it is common that I have over 1,000 rows of tasks in each project. I envisage that I need a macro to achieve the above and my script skills are non-existent. Help please….

    Sunday, March 16, 2014 11:32 PM

All replies

  • Hamish3,

    Here's a crude first cut at a macro to do what you want. Try it.

    Enter the daily rate in the Standard Rate field on the Resource Sheet for each resource. To see the cost for each resource assignment, add the Cost1 field to the Resource Usage view. Note: the normal Cost field will not give accurate values and there is no way to make it do so.

    Sub DayTripper()
    Dim t As Task
    Dim a As Assignment
    Dim r As Resource
    Dim NumDa As Integer
    Dim MPD As Single, PartDa As Single, PR As Single
    Dim RR As String
    Dim aSt As Date
    MPD = ActiveProject.HoursPerDay * 60
    For Each r In ActiveProject.Resources
        RR = r.PayRates(1).StandardRate
        PR = CSng(Mid(RR, 2, InStr(1, RR, "/") - 2))
        For Each a In r.Assignments
            If DateValue(a.Start) <> aSt Then
                NumDa = CInt(a.Work / MPD)
                PartDa = CDec(a.Work / MPD) - NumDa
                If PartDa > 0 Then NumDa = NumDa + 1
                a.Cost1 = NumDa * PR
                aSt = DateValue(a.Start)
            End If
        Next a
    Next r
    End Sub


    Monday, March 17, 2014 3:17 AM