change project timeline to show hours based on working days, weeks or months

Question

The given file shows the proper hours spread for projects, however, its timeline in range I5:JI5 doesnt show the correct working Months. The workdays come ok, the weeks i am assuming 5 workdays in a week, and the month i want to calculate accurately the no of working days in a month. for now, i am assuming 22 working days in a month. however the resultant workdays function does not yield me the right dates in the timeline.

e.g.

if you select DAY from dropdown and see the Actual End Dates, the days are all working days (excluding Sat,Sun) which is fine.
if you select WEEK from dropdown and see the Actual End Dates, the week dates are not the same. Also the hours are not falling correctly under the dates in the timeline.
if you select MONTH from dropdown and see the Actual End Dates, the month dates also are not the same. Also the hours are not falling correctly under their respective dates in the timeline.

what i mean is, ideally for all 3 options, the Actual End Dates should be the same and the hours should start from the Start Date on the timeline.

Here are the screenshots for the 3 options:

Here's the updated file (Shareable link):

PSchedule Template.xlsx

No-one's going to create an account to look at your file - you need to create a shareable upload. — Tim Williams
What do you mean by when selecting Week, the End date is not the same? The same to what end of week (Friday)? What should be the value? — Jules
@TimWilliams, it is a public shareable link from my onedrive account. anyone with this link can edit or download this file. let me know if you want me to email it to you. Thanks. — sifar
If you see the Actual End Date, it is a little further than the Est. End Date. I put it there so that i could know what is the real end date, if the project hours are spread across the timeline as well as adjusted daily to 8 hours (changeable). Now, if i am right, even if i change the dropdown to show a Week or Month view, the Actual End Date should still reflect the same, as the hours would aggregate under that date....or am i wrong? — sifar

Jules Jules · Accepted Answer · 2016-06-21T01:22:34

I have to say, this is one of those interesting questions. Took me a while to come up with a simple solution. I leave the how to populate the value on worksheet to OP :).

Dim aWork()
Dim iNoOfDay As Integer
Dim n As Integer
Dim dStart As Date
Dim dEnd As Date

Dim sCurPeriod As String
Dim sPeriod As String

Dim iCurPeriod As Integer
Dim dTotalPeriod As Double

sPeriod = "W" ' W = Weekly, M = Monthly
dStart = CDate("10 Jan 2016")
dEnd = CDate("12 Feb 2016")

iNoOfDay = DateDiff("d", dStart, dEnd)
' 0 - date, 1 - work hours, 2 - period starts from 1
ReDim aWork(iNoOfDay, 2)

iCurPeriod = 0

For n = 0 To iNoOfDay
    aWork(n, 0) = DateAdd("d", n, dStart)

    ' Set to 0 on weekends 1 = sunday, 7 = saturday may be different depending on your regional setting
    aWork(n, 1) = IIf(DatePart("w", aWork(n, 0)) <> 1 And DatePart("w", aWork(n, 0)) <> 7, 7.5, 0)

    If sPeriod = "W" Then
        If sCurPeriod <> DatePart("yyyy", aWork(n, 0)) & DatePart("ww", aWork(n, 0)) Then
            sCurPeriod = DatePart("yyyy", aWork(n, 0)) & DatePart("ww", aWork(n, 0))
            iCurPeriod = iCurPeriod + 1
        End If
    ElseIf sPeriod = "M" Then
        If sCurPeriod <> DatePart("yyyy", aWork(n, 0)) & DatePart("m", aWork(n, 0)) Then
            sCurPeriod = DatePart("yyyy", aWork(n, 0)) & DatePart("m", aWork(n, 0))
            iCurPeriod = iCurPeriod + 1
        End If
    End If

    aWork(n, 2) = iCurPeriod

Next

iCurPeriod = 0
For n = 0 To iNoOfDay
    If iCurPeriod <> aWork(n, 2) Then
        Debug.Print iCurPeriod, dTotalPeriod
        iCurPeriod = aWork(n, 2)
        dTotalPeriod = 0
    End If

    dTotalPeriod = dTotalPeriod + aWork(n, 1)
Next

Debug.Print iCurPeriod, dTotalPeriod

EDIT: The total per period calculation can be done in the first for next, I did it for the debugging.

change project timeline to show hours based on working days, weeks or months

1 Answers