Prev: Matching two sets of data?
Next: Data Validation drop-down width, with named range source (XL03 and
From: froggygremblin on 23 Mar 2010 19:03
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
From: UKMAN on 24 Mar 2010 12:06
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. ukman1(a)hotmail.com.

Cheers and good luck

UKMAN

"froggygremblin" wrote:

> 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
From: froggygremblin on 24 Mar 2010 14:49
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

"UKMAN" wrote:

> 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. ukman1(a)hotmail.com.
>
> Cheers and good luck
>
> UKMAN
>
> "froggygremblin" wrote:
>
> > 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
 | 
Pages: 1
Prev: Matching two sets of data?
Next: Data Validation drop-down width, with named range source (XL03 and