Help distributing hours over a year

[shuber1179]

Hello
I need help with this spreadsheet getting the "total estimated hours" to automatically distribtue evenly over the year based on the expected completion date. Any help would be great as I have been googling for days and this is more than a bit above my excel working knowledge.

[shuber1179]

Is this even possible?

[daddylonglegs]

Is this even possible?

I think so but I'm not sure how you want it to work. Do the hours need to show every day....or just weekdays.....or once a week....or something else?

Also is there a start date, I don't see one specified - do the hours just start at April?

[djapigo]

I see the Expected Date of Completion, but where is the Start Date?

Also, do you need the Hours during the weekend too? Holidays?

If possible, can you give an example of what you want to see?

In your sample, in row 12, you have 24 hours... do you want to spreadout from 1/1/2013 to 10/11/2013? You want to see the number of hours for each day between these 2 dates?

[shuber1179]

There is no start date, so April would be the beginning. Weekdays only. This is basically for resource allocation to show that we are over extended and under staffed.

Any help you can offer is greatly appreciated.

[daddylonglegs]

To make this simpler I would change the values in row 10 to dates, e.g. in K10 enter the first date 1-April-2013, custom format as d to show the day only (so it looks the same as before) and then copy all the way across....

.....and then in K11 you can use this formula copied all the way across and down the columns

=IF(OR($E11="",K$10>$F11,WEEKDAY(K$10,2)>5),"",$E11/NETWORKDAYS($K$10,$F11))

See attached where I entered that formula in rows 11 to 15 only

[shuber1179]

Awesome!!! Thank you sooo much!!!!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[shuber1179]

Thank you. Yes I am new to this and did know how to do that.