• 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.

Work time calculation

P

Pofski

Member
Hello everybody,

I am looking for a way to achieve the following.
I have an excel table that gets filled as we process through the year. It contains the requested jobs, the day the job request was recieved, the day that it due, and the estimated time it would take to complete the job.

i will add an excel file with an example to clarify it a bit while i explain the idea.
What i am trying to obtain is a way to show the amount of hours of work, per person, per week in a selectable form.

In my example, John has a couple of assignments. i would like to be able to select john, the weeknumber and the year, and have the total amount of work that there is planned per week (or per day by just dividing the hours of work with the networkdays) show up in the list.

I hope it makes sense what i am trying to explain.

Sincerely
 

Attachments

Hi,
Please clarify a few things.
How are the hours for a given week calculated? Is the assumption that a person will begin work upon receiving the customer request, and work until the due date? If so, is the assumption that the work will be evenly divided between those days?
For example, if the customer request came in on Sep 1, and the Due date is Sep 30, and the estimate for John is 40 hours, will those 40 hours be spent between Sep 1 and Sep 30 evenly?
 
Hi Sajan.

You are correct in your assumptions, but i would assume that the start day would be the request day +1 (if the request is received at noon, or in the evening). The hours would be spend between Sep 2 and Sep 30, but divided over working days (not weekends).
 
Hi,
See the attached worksheet. Instead of calculating by week number, the calculation is using the time period you specify. (I noticed that you had a "spinner" controlling the week number based on the period specified. As such, net effect should be the same.)

I setup a new area (shown with a rectangle) where I included the formulas.

Please validate the calculations. Let me know if you need an explanation of the formulas. Once you validate the calculations, we can look at optimizing the formulas, if needed.

Cheers,
Sajan.
 

Attachments

Hi Sajan,

It looks like it is working, but i will be honest, i'm lost in the formula. o_O
I have no experience using the mmult command, and i'm staring at it, and it doesn't enter the brain :D (need more coffee i think)
This in combination with the min/max you are using,....

Could you please explain this to me?
 
I think MMUT multiples between two arrays...
Say 1,2,3 in Col.A and 2,3,4 in Col.B and you use a formula =MMULT(Max(A1:A3),Max(B1:B3))
Then this will give you the max values in both the arrays and then Multiply the same..

Hope this clears your doubt...
 
Hi Pofski,
Before we go over the formulas, let me start with the approach I used for the calculation.

To determine the hours for a person for a specific period of time, we will first need to determine how many days overlap with the selected period.
Let us use an example:
  1. Assume the period of interest is June 1 to June 5
  2. John worked May 15 to June 11 (which is 20 working days) for a total of 40 hours.
  3. This means that John worked 40 hrs / 20 days = 2 hrs/day.
  4. The number of working days between June 1 and June 5 is 3 days.
  5. That means that John's hours for June 1 to June 5 is 3 days * 2 hrs/day = 6 hours.

The above approach works when working with a single row of data. When working with multiple rows of data, we will need to tweak our approach as follows:
  1. First determine the total time period where John was scheduled for work. This would be the minimum date of all the start dates, to the maximum of all due dates. (With the supplied data, this becomes May 6 to June 14)
  2. Then determine the number of days in each work assignment that overlaps with the Total Time Period.
  3. Divide the estimated hours for each work assignment by the number of overlapping days for each work assignment to get the hours per day.
  4. Then determine the number of days of overlap between the time period of interest (say June 10-June 14) and the Total Time Period (May 6-June 14)
  5. Multiple results from [4] with the results from [3]

Now let us examine the formulas:
  1. To calculate the minimum of all start dates for John we can use =MIN(IF(Translator=L2, StartDate)) where L2 refers to "John"
  2. To calculate the maximum of all due dates for John we can use =MAX(IF(Translator=L2, DueDate)) where L2 refers to "John"
  3. This allows us to calculate the working dates (let us refer to it as "Total Time Period for John") between the MIN and MAX dates, using the following formula:
WORKDAY(O15,ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1)))
Here O15 is the MIN date, and P15 is the MAX date.

4. Next we need to calculate the overlap between the startdate and due date for each assignment. Using the named ranges called "StartDate" and "DueDate", this then becomes the following comparison:
(TRANSPOSE(WORKDAY(O15,ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))))>=StartDate)*(TRANSPOSE(WORKDAY(O15,ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))))<=DueDate)

This returns a value of 1 where there is a date overlap, and zero if not.

5. MMULT can then be used to add up each row in this array to get a result array like {2;4;0;5} indicating that the first assignment has 2 days, second has 4 days, etc.

6. Next we divide the estimated hours for each assignment by the above array. Using the named range "Estimate", this becomes the following:
Estimate/MMULT(...)

7. We need to do a similar calculation to determine the number of days of overlap between the Total Time Period for John and the specific time period of interest (say June 10-June 14)

We then multiply the results from [6] and [7], and then SUM it to get the final result.

Putting it all together, we get the following formula:
=SUM(IFERROR(Estimate/MMULT((TRANSPOSE(WORKDAY(O15,ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))))>=StartDate)*(TRANSPOSE(WORKDAY(O15,ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))))<=DueDate), ROW(OFFSET(A$1,,,NETWORKDAYS(O15,P15)-1))^0),0) * MMULT((TRANSPOSE(WORKDAY(M15,ROW(OFFSET(A$1,,,NETWORKDAYS(M15,N15)-1))))>=StartDate)*(TRANSPOSE(WORKDAY(M15,ROW(OFFSET(A$1,,,NETWORKDAYS(M15,N15)-1))))<=DueDate), ROW(OFFSET(A$1,,,NETWORKDAYS(M15,N15)-1))^0))

I am sure this can be simplified further. But hope it helps you in understanding the above approach and formula.

Cheers,
Sajan.
 
You must log in or register to reply here.
Back
Top