Spreading manhours over partial month

[Faizah1]

I am trying to spread hours over 6 months (eg.) but the first and last months are not full month. What formula do I use to calculate partial months? Right now I have the hours spread evenly over the 6 months as if they are all 6 full months. But this is not true. How do I solve the partial months issue?

Thank you,
Faizah

=IF(J$5="","",IF(AND($H256>J$5,$H256<K$5),$F256,IF(AND($H256<=J$5,$I256>=J$5),$F256,"")))

where H256 is the START and I256 is the FINISH

=D256/((I256-H256)*12/365)

where D256 is the hours to spread if all months were full.

[shg]

There are numerous examples in the workbook at https://www.box.com/shared/7xm2kt408n

[Faizah1]

Anybody else has a different answer?

[shg]

Looking for a different answer, or a different method, or not understand the examples, or disagree with the results?

[Faizah1]

Thanks for your response. The NETWORKDAY function calculates 5 working day instead of calendar days. WORKDAY function does the same. The closest I got to was the DAYS360 function that calculates based on 12 months * 30 days each month. Your sample was great and helps with smaller data range but I am trying to spread 5 million hours over 6 years and I got to 87% of the hours. 95% would have been acceptable. I stuck with the WORKDAYS360 function because that was the closest I could get to using what was availble in Excel.

Thanks again,
Faizah

[daddylonglegs]

Example #4 in shg's worksheet should calculate the actual days of a date range within each month - you could multiply that by the total hours divided by the total days to get an hours figure for each month, so a 31 day month would get more hours than a 30 day month and partial months will get a pro-rata number of hours assigned, is that what you need?