• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hours Schedule by Role

I don't know if what I want to do is even possible. I know it certainly is not with my current level of knowledge!!

I have added an attachment.

The first sheet, "HoursCostDistribution" is part of a tool that I receive for each new project for which my company is contracted to perform. The second sheet, "GOAL" is an example of what I want to create based on the first sheet.

As you can see, in the "HoursCostDistribution" sheet, there are Start and Stop dates for a particular type of work to be done (Code A, B, C, etc. in column B). There is also the duration of days for the work to be performed in column G. The roles that are to perform the work are across the top in Row 3.

In the "GOAL" sheet, I have listed the Roles to perform the work in column A, and the months/years during which the project is supposed to run across the top in Row 1.

What I want, is for each role to have the hours allocated to it for each given activity (codes in column B of first sheet) under the appropriate month column.

Is this possible!?! Please let me know if you have any questions!

Thank you for looking!!!
 

Attachments

  • Hours Schedule by Role.xlsx
    55.7 KB · Views: 3
Take one or two examples and fill in the result you expect to see on the Goal worksheet, then we should be able to ascertain exactly what you need
 
Take one or two examples and fill in the result you expect to see on the Goal worksheet, then we should be able to ascertain exactly what you need

Hi David,
Thanks for the response. I have attached an updated file with a copy of the "HoursCostDistribution" (HoursCostDistribution (2)) sheet. In this copy I have deleted all of the roles except for "PM" in column H. As you look down column H, you will see that the PM is to perform 54 hours of work in total for Code A. These 54 hours are to be distributed across 103 days (04May2016 - 15Aug2016). Next is 115 hours for Code B, and then 26 hours for Code G, etc....

In columns I:AK, I added a header row with the months that the project will take place. I didn't use perfect math, but if you look at I5:L5, you will see hours that add up to 54 which is the total hours for Code A (cell H5). I did the same thing for each Code for which there were hours in column H. I basically just distributed the hours in column H across the Start and Stop months in columns E and F.

Then in Row 58, I added all of the hours for each month. I pasted the row 58 hours in row 8 in the "GOAL" sheet, because this is ultimately what I want in that sheet. BUT, I want it for each role that has hours in the original "HoursCostDistribution" sheet.

I hope this helps, and thanks again!
 

Attachments

  • Hours Schedule by Role2.xlsx
    62.6 KB · Views: 6
Last edited:
This workbook links the HoursCostDistribution (2) worksheet to the HoursCostDistribution worksheet using a Data Table to pass each value from the GOAL worksheet through as a Filter

hard to describe but it works a treat
 

Attachments

  • Hours Schedule by Role2.xlsx
    83.7 KB · Views: 14
This workbook links the HoursCostDistribution (2) worksheet to the HoursCostDistribution worksheet using a Data Table to pass each value from the GOAL worksheet through as a Filter

hard to describe but it works a treat

Wow! I don't understand it, but wow. I will have to look at this tomorrow when I am fully awake and see if I can make sense of it, but it looks like it works perfectly! I don't think I will ever get to your level of awesomeness. Thank you!
 
Let me see if this explanation helps you

1. The Goal ws is a Data Table
It uses the Left column as the Input to the Data tables Column Input Cell
This places each value in Column A into cell A1
The Data Table then returns the values into Row 2
Which is simply linked to the Sum Row, Row 58 on HoursCostDistribution (2)

2. HoursCostDistribution (2) is the Schedule
This worksheets takes the current value from Goal!A1 and places that in H3
Then Cells H4:H56 update and retrieve the new values from the HoursCostDistribution worksheet

Then the schedule calculates
The schedule is based around the formula in I5:
=IFERROR(IF(AND($E5<J$2,$F5>=I$2),IF(J$2>$F5,$F5-I$2,MIN(J$2-$E5,J$2-I$2)),0)*$H5/$G5,0)

which basically pro-ratas the values accross the days in each month

3. HoursCostDistribution worksheet
This is your source data, no changes here

To note is that the Schedule or Goal ws's are not linked to the dates columns ie: are hard coded. So you need to be sure that everything lines up
The same with the order of the rows in HoursCostDistribution and HoursCostDistribution worksheet (2)
 
Hui, what an interesting solution! Up until now, I have never explored What-if analysis in Excel. Its something I will dig into now though.
And there was me going down the route of re-arranging the data for a pivot table solution (which I'll still do, in the passage of time).

A couple of questions, highlighted in blue in my ramblings below.

I note that in Hui's solution in sheet HoursCostDistribution (2) column G that the Variance is calculated and mostly agrees with the Duration column in the HoursCostDistribution sheet. Mostly being the operative word. There are a few which are different. They are for Codes L, T and AT. Code T dates are just a typo - no problem there. In fact for your data it doesn't matter at all since there are no hours against the other codes (L and AT) anyway.
However, if there were data against these 1 day codes I suspect both Hui's and my solutions would be tripped up.
So this is my question: where we have:
upload_2017-10-21_11-50-49.png
that 1 in the rightmost cell means that the two dates are both days which count. By the same token, if you had:
upload_2017-10-21_11-54-33.png
you'd expect 2 to be correct, right?.
You have elsewhere:
upload_2017-10-21_11-58-38.png
showing 14 days, but if you follow the same logic as above, that 14 should be 15 days. The same applies to all the other rows.
This matters when distributing hours amongst the months, especially if there are many months and few hours.
So what to do? How are those values in the Duration column currently arrived at?


A supplemental question, which likely would make your GOAL table more realistic/accurate, is: would you want to account only for business days (ie. excluding weekends (and perhaps holidays too)) when distributing the hours?

As an example of how this may be important; what if the Stop Date is the 2nd of the month, but both the 1st and 2nd fall on a weekend? That month wouldn't have any business days at all to distibute hours to. Would it make sense to show/include any hours at all for that month for that Code?

It would be quite easy for me to exclude weekends (and even holidays - given a list of holiday dates somewhere on a sheet) since I'm using a small macro to re-arrange the data.
 
Last edited:
Thanks @Hui, I will read it. No worries, I'll supply Spandex and cape.
My internet browser now has so many tabs of pages I intend to read it's slowing it up, but this last one will be one of the earliest to be read!
 
I have updated the Formulas to do the distribution per the Number of Days (Mon-Fri) in each Month, as opposed to Total days

I have also changed the Schedule to include Holidays as non-production days
I made up a list of Random Holidays below the Schedule
So the production is spread between the first and last days excluding Week ends and Holidays
I have added a check Column at the end of the Schedule

I have renamed the Worksheets to clarify there functions

All the data transfers from the First worksheet to the second worksheet now

Enjoy
 

Attachments

  • Hours Schedule by Role2 - Ex WE.xlsx
    85.8 KB · Views: 8
Last edited:
In the attached, a pivot table in the GOAL sheet at cell A105.
Its source data (a named range PivotSourceRange) is on the HoursCostDistribution sheet at cell B59. This data was generated by a macro.
That data uses business days only and excludes holidays (also a named range Holidays at cell DF4 of the HoursCostDistribution sheet).
The pivot table only shows rows and columns where there are data, so if a role has no time assigned, then it won't show. Likewise for months across the top.
The code ignores the Duration column and uses only the Start and Stop columns (E & F) to determine duration.
I've used the same imaginary holiday dates as Hui and I'm pleased to say our results match exactly.
There's a little extra I added to the pivot if it's any use; at cell B103 of the GOAL sheet there's a filter for Codes, so you can limit the data you see to just one or more Codes easily.
Being a pivot, you can re-arrange it as you want and produce charts at the drop of a hat. Or add more pivot tables.
 

Attachments

  • Chandoo36150Hours Schedule by Role.xlsb
    257.5 KB · Views: 4
I have updated the Formulas to do the distribution per the Number of Days (Mon-Fri) in each Month, as opposed to Total days

I have also changed the Schedule to include Holidays as non-production days
I made up a list of Random Holidays below the Schedule
So the production is spread between the first and last days excluding Week ends and Holidays
I have added a check Column at the end of the Schedule

I have renamed the Worksheets to clarify there functions

All the data transfers from the First worksheet to the second worksheet now

Enjoy

Thank you, Hui. This is amazing. You've made me happy by what you have provided, but you also make me feel a little bad about myself, as I am slowly arriving at the conclusion that, perhaps, I simply am not sufficiently intelligent to ever be able to come up with something like you did. I am going to pout a little more about being a dummy, and then I will go back to learning about Data Tables.

I do have a question about the data that you used to create your "Goal" sheet. In the latest workbook you attached, you have three sheets, "HoursCostDistribution", "Schedule", and "Goal". Originally, the "Schedule" sheet was named "HoursCostDistribution (2)", and the only reason that sheet existed is because I was trying to make clear to David Evans, who asked for an example of what I wanted, what I wanted to do. In that sheet, I deleted columns for all roles except for PM (just because that's the role that won the coin flip for my example). I then looked at cell H5 (54) which shows me the total hours that the PM is supposed to spend on Code A (cell B5) from 04May2016 to 15Aug2016. Then, I spread the 54 hours across the months I5:L5. Note: I DID NOT USE MATH or a fancy formula to do that ... just estimates. Then, I did the same thing for each row in which there were hours in column H.

I guess my question is, since the Schedule sheet (HoursCostDistribution (2)) is something I just added for the purpose of explaining my needs, is it possible to do what you did if all you had to work with was the original "HoursCostDistribution" sheet, since that is all I have to start with. I don't start out with the GOAL sheet either, but that is so simple to create using the roles in row 3 of the HoursCostDistribution sheet that it doesn't matter.

Thank you!
 
In the attached, a pivot table in the GOAL sheet at cell A105.
Its source data (a named range PivotSourceRange) is on the HoursCostDistribution sheet at cell B59. This data was generated by a macro.
That data uses business days only and excludes holidays (also a named range Holidays at cell DF4 of the HoursCostDistribution sheet).
The pivot table only shows rows and columns where there are data, so if a role has no time assigned, then it won't show. Likewise for months across the top.
The code ignores the Duration column and uses only the Start and Stop columns (E & F) to determine duration.
I've used the same imaginary holiday dates as Hui and I'm pleased to say our results match exactly.
There's a little extra I added to the pivot if it's any use; at cell B103 of the GOAL sheet there's a filter for Codes, so you can limit the data you see to just one or more Codes easily.
Being a pivot, you can re-arrange it as you want and produce charts at the drop of a hat. Or add more pivot tables.

Thank you, P45cal! I will explore your solution more closely when I return from an afternoon appointment.
 
Carlos

Firstly, You shouldn't feel bad about yourself and your Excel skills. You will get there.
I have been using spreadsheets since Visi-calc which is older than Excel and so I learned the hard way.
But the great thing now is that you have the resources like Chandoo.org and us at the Forums to lean on.

Please see the model with the schedule taken back to onto the HoursCostDistribution worksheet.
 

Attachments

  • Hours Schedule by Role3.xlsx
    81.5 KB · Views: 15
Back
Top