report by date from a list of records
in [Functions]
|
From: UKMAN on 9 Mar 2010 11:46 Roger, many thanks but all I get is zeros? I have ensured that the date start etc are covered by the month end dates:) (d1:01) suggesttions:( Cheers "Roger Govier" wrote: > Hi > > Using the same data layout as shown by Eva in her posting > In cell D1 enter 31/10/2010 and using the fill handle with right mouse > button held down, drag across to O1, release the mouse button and choose > fill months. > Each of the cells should now be filled with the last day of each month. > If you wish, format these cells>Number>Custom>mmm to just show the month > name. > > Now enter in cell D2 > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B2&":"&$C2)), > ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))) > > Copy across to O2 > Copy D2:O2 down the page for as many rows of data that you have. > > This will give a count of the number of days falling in each month > -- > Regards > Roger Govier > > UKMAN wrote: > > Hi > > > > My bad english as I should of expresssed my comment better. > > I appreciate 2003 has pivot tables but it has to be in 2003 and I am trying > > to keep away from pivot tables as users will be printing the report and just > > want to keep it simple. :( > > > > I have adaptered Eva's suggestion to show it by buisness plan quarter and > > will look to try and get the perfect solution. i.e. calculate the actual days > > per month allocated so need to identify the month from a dd/mm/yy date, to > > match against a mm/yy and then add the days to a total for the individual for > > that month. > > > > Many thanks anyway. > > > > "Gord Dibben" wrote: > > > >> 2003 has the Pivot Table function. > >> > >> > >> Gord Dibben MS Excel MVP > >> > >> On Thu, 4 Mar 2010 09:09:02 -0800, UKMAN <UKMAN(a)discussions.microsoft.com> > >> wrote: > >> > >>> hi was trying to keep away from pivot as it has to be 2003 compatable > >>> > >>> thanks anyway > >>> > >>> "Herbert Seidenberg" wrote: > >>> > >>>> Excel 2007 PivotTable > >>>> No code, no formulas: > >>>> http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_03_10a.xlsx > >>>> > >>>> . > >>>> > >> . > >> > . >
From: UKMAN on 9 Mar 2010 11:48 Herbert Thanks but I am trying to keep away from pivot tables and also it has to be done in 2003. I know 2003 has pivot tables :) but I have to poduce a simple report for the users to see. UKMAN1 "Herbert Seidenberg" wrote: > Excel 2007 PivotTables > Incorporated Roger's jolly good job. > http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_03_10a.xlsx > > . >
From: UKMAN on 10 Mar 2010 10:33 Roger, We are nearly there as I have cured the error I mentioned before. :) Many thanks As i am colating the individuals total values of the rows into 1 line of the report I need to match the name see below my addition to your formula. {=IF($H$16:$H$30=A4,SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B4&":"&$C4)),ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))),)} H16:h30 is a list of names, A4 is the name of the student on the row that the course dates are for. Using your formula I can divide the dates over the months but for some reason when I try to match a name (a4) against the list (h16:h30) I only ever match the first name i.e. what is in h16??? other wise I get a "FALSE" statement. Ideas please.... Cheers UKMAN "Roger Govier" wrote: > Hi > > Using the same data layout as shown by Eva in her posting > In cell D1 enter 31/10/2010 and using the fill handle with right mouse > button held down, drag across to O1, release the mouse button and choose > fill months. > Each of the cells should now be filled with the last day of each month. > If you wish, format these cells>Number>Custom>mmm to just show the month > name. > > Now enter in cell D2 > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B2&":"&$C2)), > ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))) > > Copy across to O2 > Copy D2:O2 down the page for as many rows of data that you have. > > This will give a count of the number of days falling in each month > -- > Regards > Roger Govier > > UKMAN wrote: > > Hi > > > > My bad english as I should of expresssed my comment better. > > I appreciate 2003 has pivot tables but it has to be in 2003 and I am trying > > to keep away from pivot tables as users will be printing the report and just > > want to keep it simple. :( > > > > I have adaptered Eva's suggestion to show it by buisness plan quarter and > > will look to try and get the perfect solution. i.e. calculate the actual days > > per month allocated so need to identify the month from a dd/mm/yy date, to > > match against a mm/yy and then add the days to a total for the individual for > > that month. > > > > Many thanks anyway. > > > > "Gord Dibben" wrote: > > > >> 2003 has the Pivot Table function. > >> > >> > >> Gord Dibben MS Excel MVP > >> > >> On Thu, 4 Mar 2010 09:09:02 -0800, UKMAN <UKMAN(a)discussions.microsoft.com> > >> wrote: > >> > >>> hi was trying to keep away from pivot as it has to be 2003 compatable > >>> > >>> thanks anyway > >>> > >>> "Herbert Seidenberg" wrote: > >>> > >>>> Excel 2007 PivotTable > >>>> No code, no formulas: > >>>> http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_03_10a.xlsx > >>>> > >>>> . > >>>> > >> . > >> > . >
From: UKMAN on 10 Mar 2010 11:50
Roger sorry about this but I have noticed that your formula includes weekend days not just working days :) is there a way of using networkdays with this so only shows working days in the result??? cheers UKMAN1 "Roger Govier" wrote: > Hi > > Using the same data layout as shown by Eva in her posting > In cell D1 enter 31/10/2010 and using the fill handle with right mouse > button held down, drag across to O1, release the mouse button and choose > fill months. > Each of the cells should now be filled with the last day of each month. > If you wish, format these cells>Number>Custom>mmm to just show the month > name. > > Now enter in cell D2 > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT($B2&":"&$C2)), > ROW(INDIRECT(DATE(YEAR(D$1),MONTH(D$1),0)+1&":"&D$1)),0))) > > Copy across to O2 > Copy D2:O2 down the page for as many rows of data that you have. > > This will give a count of the number of days falling in each month > -- > Regards > Roger Govier > > UKMAN wrote: > > Hi > > > > My bad english as I should of expresssed my comment better. > > I appreciate 2003 has pivot tables but it has to be in 2003 and I am trying > > to keep away from pivot tables as users will be printing the report and just > > want to keep it simple. :( > > > > I have adaptered Eva's suggestion to show it by buisness plan quarter and > > will look to try and get the perfect solution. i.e. calculate the actual days > > per month allocated so need to identify the month from a dd/mm/yy date, to > > match against a mm/yy and then add the days to a total for the individual for > > that month. > > > > Many thanks anyway. > > > > "Gord Dibben" wrote: > > > >> 2003 has the Pivot Table function. > >> > >> > >> Gord Dibben MS Excel MVP > >> > >> On Thu, 4 Mar 2010 09:09:02 -0800, UKMAN <UKMAN(a)discussions.microsoft.com> > >> wrote: > >> > >>> hi was trying to keep away from pivot as it has to be 2003 compatable > >>> > >>> thanks anyway > >>> > >>> "Herbert Seidenberg" wrote: > >>> > >>>> Excel 2007 PivotTable > >>>> No code, no formulas: > >>>> http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_03_10a.xlsx > >>>> > >>>> . > >>>> > >> . > >> > . > |