excel distribute hours value on time plan-CodePudding

I want to distribute hours of task on basis of start date and end date considering 9 hours a day. In attachmnet you can see example which is manually filled. Is there any formula which can give this kind of result.

CodePudding user response：

You need to be able to find the beginning and the ending date. For that, you might use a Match() function. Once you have this, the following can help you:

Once you are on the most left cell (the one equal to the beginning date), the value should be MIN(<Hours>,9): if the value in the <Hours> is smaller than 9, then use that value, otherwise use 9.
When you are somewhere at the right of that most left cell, the value should be based on current_cell_attempt = <Hours> - sum(Most_Left_Cell:Adjacent_Cell): if this value is larger than 9 then put 9; if it's smaller then put the value.
Once you start having 0 values, put blanks.

CodePudding user response：

You can use following formula in first cell (E3 in my sample) and copy to the right and down:

=MIN(IF((COLUMN()>5)*(E$1>=$C3),$B3-SUM(D3:$E3),$B3),9*(E$1>=$C3)*(E$1<=$D3))

use number formatting to hide zeros, if you want