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  |