Calculate Number of Days worked in Each Month by Project

[froggygremblin]

I want to calculate the number of days worked on a project in a rolling 4
month calendar. Working Days are Mon-Thu. My sheet looks like this:

Jobname TotalDays StartDate EndDate DaysMo1 DaysMo2 DaysMo3 DaysMo4
Foo 3 3-22-10 3-25-10 3 0
0 0
Goo 25 4-2-10 5-17-10 0 17 8
0

I have used Workday to calculate my enddate but I'm having difficulty
calculating the proper month bins to put the days in. Since the calendar
rolls forward ewach month I need to consider that a job started before Mo1 or
in Mo1 or in Mo2 or in Mo4 or it'sw beyond the planning horizon. Anyone
solved this before?

Thanks for any guidence

 

[UKMAN]

Hi

I have been having issues with a training plan and was given a formula to
calc the days the course used in each month but this included weekends, and
noone has been able to help to only show workdays so not sure how you will
only do 4 days each week.

In brief my sheet you put in a start date (m7) and an end date(n7), Q5 in a
mm-yy field to match a month. Note Q5 was Jan-10 and the columns went to AB5
(dec-10) so with the formula copied across all columns if a course covered 1
or more months then this would say how many days in each month.

=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($M7&":"&$N7)),ROW(INDIRECT(DATE(YEAR(Q$5),MONTH(Q$5),0)+1&":"&Q$5)),0)))


Not sure if this will help in any way but if you do crack it please let me
know, email me if you could. (e-mail address removed).

Cheers and good luck

UKMAN

 

[froggygremblin]

Thanks for the lead. I tinkered with it a good bit. I see your issue. It
calculates the total number of days in the month that a course COULD occur
rather than the number of workdays it DOES occur. It took me awhile to
figure out how even that works - It uses the start(m7) and end(n7) dates to
calculate an array of "rows" where the first row is the serial number of the
start date and the last row is the serial number of the end date. Then it
creates a second array of "rows" from the serial for the 1st of the month in
row 5 in the current column to the serial number for whatever date you
entered in Q5:AB5 (Seems like that needs to be the last day of the month in
each column). Then it counts the matches in the first array that also occur
in the second array and that gives you a count of days in taht month that are
included in you start/Stop range. Pretty neat but not what I'm trying to do

Thanks for taking time to help out