Formula to divide data from one cell equally into

Question

I have these cells:

• Cell I7 where I put total hours a task will take, and
• Cell J7 has start time in yyww format, e.g. 1624, meaning year 2016 and week 24, and
• Cell K7 is the deadline in same format.

I have sequential week numbers in row 2, again in the same format, e.g.

• Cell L2 has 1622
• Cells on the right of L2 have incrementing week numbers: 1623, 1624,...

I want the total hours the task takes to be equally divided into the appropriate weeks. I am counting with the start week and the deadline week included. Use decimals if needed.

This sheet is only for 2016, meaning I don't go across year boundaries. For example:

Total time 100 hours. Start 1625, end 1634. Cell corresponding to column representing week 1625: 10 hours, week 1626: 10 hours, week 1627: 10 hours,... up to 10 weeks.

I want the hours divided into weeks to appear in the same row. ex. I7=50 , J7=1601, K7=1605, L7=10, M7=10, N7=10, O7=10, P7=10.

Answer 1

I can't give you a precise answer as the question is a little vague, but at the very least you need to break down the input into a more useable format that you can then reconstruct into the desired format. For example, the following formula (adapted from here will convert "1625" into "12 June 2016":

=INT((DATE("20"&LEFT(B2,2),1,1)+(RIGHT(B2,2)*7)-7)/7)*7+1

Where cell B2 is the value 1625. Do the same for the end date, then use the formula NETWORKDAYS() to work out the number of working days between these dates. From that you can then work out the number of hours per day are needed, or whatever your requirements are.

If you can formulate the question a little more precisely I could probably give a more complete answer. For example, are you trying to work out how many hours are needed per week to complete the task, or are you trying to estimate the manpower needed given, for example, 7 man-hours per day?

Answer 2

I propose a solution that would result in this output:

The input fields are located in cells I7, J7 and K7 like you described.

The formula in L7 expects the week number to appear in row 2:

=IF( AND(L$2>=$J7, L$2<=$K7), $I7/($K7-$J7+1), "")

That formula can be copied to the right and downward as far as you need it.

When some day you add weeks in row 2 for 2017, so you have a mix of 2016 and 2017 week numbers, then the above will fail for periods that cross the year boundary, since the denominator ($K7-$J7+1) will return a too large number. If you want to deal with that, you can use this formula:

=IF(AND(L$2>=$J7,L$2<=$K7), $I7/(MATCH($K7,$2:$2;0)-MATCH($J7,$2:$2,0)+1), "")

That formula requires that the start- and end week really occur in row 2.

Note that your regional settings might be different for the list separator. In that case you might need to change the commas in the formula by semicolons (;). Regional settings also influence the names of the functions (IF and AND). Please check what the Excel equivalent is in your language version.