report by date from a list of records
in [Functions]
|
From: UKMAN on 3 Mar 2010 11:21 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: Eva on 3 Mar 2010 11:51 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: Herbert Seidenberg on 3 Mar 2010 19:29 Excel 2007 PivotTable No code, no formulas: http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_03_10a.xlsx
From: UKMAN on 4 Mar 2010 12:09 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 4 Mar 2010 12:12
Eva, thanks for this but it will mean I have to add more data in i.e. to id the month and if a course goes over 2 months need to split the duration appropiately... :( will paly with what you have sent though :) 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 |