Excel formula to spread data over a period of time

  • Thread starter Thread starter PMuskie
  • Start date Start date
P

PMuskie

I have a project start and end date and have calculated the amount of time
based on those dates. I have # hours that will be spent on that project. I
need to have the # hrs spread monthly over the length of the project based on
the start and end date. any help would eb appreciated.
 
Going with the idea that if your project covers x months, and you have y
hours, formula would be y / x:

=Hours/DATEDIF(Start_Date,End_Date,"m")

Note that this uses complete months. So, a start date of 1/15/09, and end
date of 3/1/09, would cause formula to use 1 full month, so a distribution of
40 hrs per month (since its only 1 month)
 
Assumed Start Date is cell A1, End date is in Cell B

Working hours = 8 hrs

Including Saturday & Sunday =DATEDIF(A1,B1,"d")*8

Exclude Saturday & Sunday or holidays =NETWORKDAYS(A1,B1)*8
 
Back
Top