If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
report by date from a list of records
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 cellsNumberCustommmm 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 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.racks...03_03_10a.xlsx . . . |
#12
|
|||
|
|||
report by date from a list of records
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.racks...03_03_10a.xlsx . |
#13
|
|||
|
|||
report by date from a list of records
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(INDI RECT(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 cellsNumberCustommmm 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 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.racks...03_03_10a.xlsx . . . |
#14
|
|||
|
|||
report by date from a list of records
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 cellsNumberCustommmm 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 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.racks...03_03_10a.xlsx . . . |
|
Thread Tools | |
Display Modes | |
|
|