report by date from a list of records
in [Functions]
|
From: Gord Dibben on 4 Mar 2010 13:38 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 5 Mar 2010 08:17 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 5 Mar 2010 08:18 Eva I have adaptered your 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 I 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 UKMAN "Eva" wrote: > Hi > This is how I see it: > > A B C D > E > name Start End Duration Month > Colin March 1, 2010 March 4, 2010 3 3 > Colin January 5, 2010 January 15, 2010 10 1 > Colin January 3, 2010 January 4, 2010 1 1 > > Duration:Formula: DAYS360(B6,C6,TRUE) (or DAYS360(B6,C6,TRUE)+1) > Month formula: MONTH(B6) > > Report > 1 2 3 > Name Jan Feb March etc > Colin 11 > xx > > Array Formula (click ctrl+Shift+enter) > =SUM(IF($A$6:$A$16="Colin",IF($E$6:$E$16=1,$D$6:$D$16,0),0)) > > or more flexible > =SUM(IF($A$6:$A$16=G7,IF($E$6:$E$16=H4,$D$6:$D$16,0),0)) > where G7=name, H4=month number (1,2 ect) > > -- > Please click "yes" if this post helped you! > > Greatly appreciated > > Eva > > > "UKMAN" wrote: > > > Hi > > > > The list is made up of records for training courses and includes start and > > end dates and course duration, i.e. Name of person, start: 1 mar 2010, ends 4 > > Mar 2010, duration 4 days > > > > > > What I need to do is create a consolidated report to show the amount of days > > a single person had training days per month/: > > > > headings of the report are: > > > > Name - Jan - Feb - Mar etc > > Colin 0 2 3 etc > > > > Hopes this helps and gratful for any help. :) > > > > UKMAN
From: Roger Govier on 5 Mar 2010 19:36 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: Herbert Seidenberg on 6 Mar 2010 22:01 Excel 2007 PivotTables Incorporated Roger's jolly good job. http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_03_10a.xlsx
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Difference between 2 numbers Next: Disable automatic text wrap? |