How t count by week whatever is with in the week (two criteria, ma

[Guest]

I have a “Date†Colum, its first row begins with 01 Oct 06 and ends with 01
Oct 08.
I have another column “Type of Work†that has three types of work, SR, SO
and WR
I would like to draw a total number of Work Orders per week and type of work
Order (results on the other sheet)
e.e:

Weekending SR SO WO
07-Oct-06 = 10 45 15
14-Oct-06 = 30 56 102
etc…..

I tried with Pivot Table but I can cot brake them down by weeks.

Is there other way of working this out please? Perhaps formula, PivotTable
or maybe code?

Thank you,
Adnan

 

[Bob Phillips]

=SUMPRODUCT(--(Sheet1!$A$1:$A$1000>$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh
eet1!$B$1:$B$1000=B$1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob,

I appreciate your quick response. I tried it, it gives me a number but I
don’t really seam to know where it’s coming form nor what it’s calculating?

Where do I paste this formula?

Note: Sheet1 has the data, and sheet2 should have results, I have the format
on sheet2 that looks this way:

Weekend SRs SOs WRs
07-Oct-06 [formula here for each for each work type (SR, SO, WR)]
14-Oct-06 …
21-Oct-06 …

Thank you again,
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

=SUMPRODUCT(--(Sheet1!$A$1:$A$1000>$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh
eet1!$B$1:$B$1000=B$1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

That is exactly how I read your original post Adnan.

Paste the formula into B2 on sheet2, and copy down and across.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

I appreciate your quick response. I tried it, it gives me a number but I
don't really seam to know where it's coming form nor what it's calculating?

Where do I paste this formula?

Note: Sheet1 has the data, and sheet2 should have results, I have the format
on sheet2 that looks this way:

Weekend SRs SOs WRs
07-Oct-06 [formula here for each for each work type (SR, SO, WR)]
14-Oct-06 .
21-Oct-06 .

Thank you again,
Adnan

 

[Guest]

Bob,

I appreciate your efforts. I can’t seam to make it work, though. How about,
I change my question… (this would give me greater results)

I have column B that has dates. What formula should I use to return week
number in column A?

e.g:


A B
____________
1 01-Oct-06
1 01-Oct-06
1 01-Oct-06
2 08-Oct-06
2 10-Oct-06
2 11-Oct-06
2 14-Oct-06
3 05-Oct-06
3 08-Oct-06
..
..
..

Again, thank you!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

That is exactly how I read your original post Adnan.

Paste the formula into B2 on sheet2, and copy down and across.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

I appreciate your quick response. I tried it, it gives me a number but I
don't really seam to know where it's coming form nor what it's calculating?

Where do I paste this formula?

Note: Sheet1 has the data, and sheet2 should have results, I have the format
on sheet2 that looks this way:

Weekend SRs SOs WRs
07-Oct-06 [formula here for each for each work type (SR, SO, WR)]
14-Oct-06 .
21-Oct-06 .

Thank you again,
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

=SUMPRODUCT(--(Sheet1!$A$1:$A$1000>$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh
eet1!$B$1:$B$1000=B$1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a "Date" Colum, its first row begins with 01 Oct 06 and ends with
01
Oct 08.
I have another column "Type of Work" that has three types of work, SR, SO
and WR
I would like to draw a total number of Work Orders per week and type of
work
Order (results on the other sheet)
e.e:

Weekending SR SO WO
07-Oct-06 = 10 45 15
14-Oct-06 = 30 56 102
etc...

I tried with Pivot Table but I can cot brake them down by weeks.

Is there other way of working this out please? Perhaps formula, PivotTable
or maybe code?

Thank you,
Adnan

 

[Bob Phillips]

How do you determine that 01-Oct is week 1?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

I appreciate your efforts. I can't seam to make it work, though. How about,
I change my question. (this would give me greater results)

I have column B that has dates. What formula should I use to return week
number in column A?

e.g:


A B
____________
1 01-Oct-06
1 01-Oct-06
1 01-Oct-06
2 08-Oct-06
2 10-Oct-06
2 11-Oct-06
2 14-Oct-06
3 05-Oct-06
3 08-Oct-06
.
.
.

Again, thank you!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

That is exactly how I read your original post Adnan.

Paste the formula into B2 on sheet2, and copy down and across.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

I appreciate your quick response. I tried it, it gives me a number but I
don't really seam to know where it's coming form nor what it's calculating?

Where do I paste this formula?

Note: Sheet1 has the data, and sheet2 should have results, I have the format
on sheet2 that looks this way:

Weekend SRs SOs WRs
07-Oct-06 [formula here for each for each work type (SR, SO, WR)]
14-Oct-06 .
21-Oct-06 .

Thank you again,
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!


:

=SUMPRODUCT(--(Sheet1!$A$1:$A$1000>$A2-7),--(Sheet1!$A$1:$A$1000<=$A2),--(Sh

eet1!$B$1:$B$1000=B$1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a "Date" Colum, its first row begins with 01 Oct 06 and

ends
with

01
Oct 08.
I have another column "Type of Work" that has three types of work,

SR,
SO

and WR
I would like to draw a total number of Work Orders per week and

type
of

work
Order (results on the other sheet)
e.e:

Weekending SR SO WO
07-Oct-06 = 10 45 15
14-Oct-06 = 30 56 102
etc...

I tried with Pivot Table but I can cot brake them down by weeks.

Is there other way of working this out please? Perhaps formula, PivotTable
or maybe code?

Thank you,
Adnan

 

[Lori]

It is possible to group data by weeks in the pivot table if all entries
in the column are valid dates:

After you create the pivot table right click the date column and select
the group by option from the short cut menu (or use the toolbar green
arrow button). For the group by options check days, for No of days
enter 7, and then enter start date probably first monday in the range

 

[Guest]

Bob,
Just performed another search and this is what did the job. Thank you for
you efforts though.

Assuming that the date is in B1, you paste the following formula in A1. This
turns the number of the week:

=TRUNC(((B1-DATE(YEAR(B1),1,0))+6)/7)


Lori,
I would still like to perform your tip but I can't. I don’t' get that 'Group
by' option in first place, there is another one that says just ‘Group’ but
that's for rows and columns.

I am using Excel 2003 version. It’s not because of the version, is it?

BTW, I am interested in buying a book that teaches PivotTables. Any advice
on what book would be the best as far as Excel 2003 concerns?

Thank you much!
Adnan

--
Please post all your inquiries on this community so we can all benefit -
Thank you!

It is possible to group data by weeks in the pivot table if all entries
in the column are valid dates:

After you create the pivot table right click the date column and select
the group by option from the short cut menu (or use the toolbar green
arrow button). For the group by options check days, for No of days
enter 7, and then enter start date probably first monday in the range

 

[Lori]

This worked for me, with your data below:

Date No
01-Oct-06 1
01-Oct-06 1
01-Oct-06 1
08-Oct-06 2
10-Oct-06 2
11-Oct-06 2
14-Oct-06 2
05-Oct-06 3
08-Oct-06 3

Pivot table Group options:
By: Days, Starting at: 25/09/2006 (Monday before start date)

Sum of No
Date Total
25/09/2006 - 01/10/2006 3
02/10/2006 - 08/10/2006 8
09/10/2006 - 15/10/2006 6

I find the best way to learn is to experiment, make a small table and
try the various different options. Don't be afraid, if you make a
mistake you can undo but always save a separate copy of your raw data
which you can refer back to and try different methods on.
Get new ideas by following up suggestions in books, newsgroups or
websites, I can't refer you to any specific books on pivot tables but
Walkenbach is always reliable..

Bob,
Just performed another search and this is what did the job. Thank you for
you efforts though.

Assuming that the date is in B1, you paste the following formula in A1. This
turns the number of the week:

=TRUNC(((B1-DATE(YEAR(B1),1,0))+6)/7)


Lori,
I would still like to perform your tip but I can't. I don't' get that 'Group
by' option in first place, there is another one that says just 'Group' but
that's for rows and columns.

I am using Excel 2003 version. It's not because of the version, is it?

BTW, I am interested in buying a book that teaches PivotTables. Any advice
on what book would be the best as far as Excel 2003 concerns?

Thank you much!
Adnan