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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Monthly Spread sheet. 12
elapsed dates between start and end date and time 2
calculate total hours based on dates 5
How do I spread numbers over several months? 3
Time calculation for a givenn period 1
Excel Sumproduct 0
How to distribute/spread/map cost of project across different year 3
Formula required for # of days Calculation 3

Back
Top