Attributing Monthly Total to Date Ranges

Hi all,

Hoping someone can help me out.

I'm trying to calculate monthly utilization for projects. I have a column for Project Hours (by project) and two columns for Start Date and End Date.

I want to see if i can pull the hours attributed to the project, have those hours take into account the Start Dates and End Dates (NetworkDays), then apply those hrs to the appropriate month column (auto-populated through a formula I'm assuming or using data on a separate data tab)

Any ideas?

Hi DILIPandey,

Thanks for the quick response.

I was aiming on using 'working hours per month', so 8 hour days 5 days a week (minus holidays) for however many weeks in that specific month. I think the NetworkDays function takes this into account, I simply multiplied that by 8 to get my NetworkHours.

What I'm hoping to achieve is this. I input the hours for a project, and the start and end date for that project. On the back end those hours and the date range are calculated, and it enters the appropriate hours into the month cell for that project. For example, a project running Jan 2-Feb 20th, has 288 'workable hrs' (no OT being considered), I'd like to see the Jan cell then read 176 (as it's the total number of 'workable hours' in Jan) and the Feb cell to read 112, as it's the remaining amount of hours left.

Any thoughts?

Hi

1) Change the details in row 2 to be an actual date. So L2 would be 1/1/2012, M2 would be 1/2/2012 etc. Format the columns to only show the month if required.

2) L3: =IF(NETWORKDAYS(MAX($I3,L$2),MIN($J3,EOMONTH(L$2,0)))*8<0,0,NETWORKDAYS(MAX($I3,L$2),MIN($J3,EOMONTH(L$2,0)))*8)

I'm not really sure if you want to attribute the hours from column K or column F. The above works on the hours from column K.

HTH

rylo