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 a

Name - Jan - Feb - Mar etc
Colin 0 2 3 etc

Hopes this helps and gratful for any help. :)

UKMAN

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 a

Name - Jan - Feb - Mar etc
Colin 0 2 3 etc

Hopes this helps and gratful for any help. :)

UKMAN

Excel 2007 PivotTable
No code, no formulas:
http://c0718892.cdn.cloudfiles.racks...03_03_10a.xlsx

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

.

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 a

Name - Jan - Feb - Mar etc
Colin 0 2 3 etc

Hopes this helps and gratful for any help. :)

UKMAN

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

.

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

.


.

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 a

Name - Jan - Feb - Mar etc
Colin 0 2 3 etc

Hopes this helps and gratful for any help. :)

UKMAN

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

.

.

Excel 2007 PivotTables
Incorporated Roger's jolly good job.
http://c0718892.cdn.cloudfiles.racks...03_03_10a.xlsx

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

.

.

.

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

.

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

.

.

.

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

.

.

.