Hi All,

I have worksheet, which gives the date and status of the particular
task is completed or not.
sheet 1
Col A Col B Col C Col D
sl.no Date A/C Status
1 02-jan-09 x completed
2 02-jan-09 y completed
3 02-jan-09 z pending

and goes on....

In sheet2 I want a report.
Col A Col B Col C
Weeks completed pending
Jan 1-2
Jan 5-9
Jan 12-16
Jan 19-23
Jan 26-30

in cell B2 below completed I use this formula ( Array Formula)

=COUNT(IF(MONTH(sheet1!$B$2:$B$500)=1,IF(DAY(sheet 1!$B$2:$B$500)=1,IF
(DAY(sheet1!$B$2:$B$500)<=2,IF(sheet1!$C$2:$C$500= sheet2!$B$1,)))))

it will give me the result as 2, for the First week of Jan,
For Second week i will change the days between 5 & 9.

Similarly i will do for the Feb, Mar,.......

Problem is that I personally feel that this is very big...
I guess we can use Pivot table ... by grouping the date , weekly data
we can get...

Any one knows better formula than the above.

And also I want how to convert the above formula to VBA ?

I used this formula
Application.worksheetfunction.count (iif(expre,true,false)......
same way the formula was created but it is giving error 13, type
mismatch
After analysing the problem - what I came know is Month function used
in vba only for single value.

Can we use what I have in formula ?

=SUMPRODUCT(--(Sheet1!$B$2:$B$50<""),--(Sheet1!$D$2:$D$50=B$1),
--(MONTH(Sheet1!$B$2:$B$50)=MONTH(LEFT($A2,FIND("
",$A2)-1)&"-"&YEAR(TODAY()))),
--(DAY(Sheet1!$B$2:$B$50)--MID($A2,FIND(" ",$A2)+1,FIND("-",$A2)-FIND("
",$A2)-1)),
--(DAY(Sheet1!$B$2:$B$50)<=--MID($A2,FIND("-",$A2)+1,9)))

--
__________________________________
HTH

Bob

wrote in message
...
Hi All,

I have worksheet, which gives the date and status of the particular
task is completed or not.
sheet 1
Col A Col B Col C Col D
sl.no Date A/C Status
1 02-jan-09 x completed
2 02-jan-09 y completed
3 02-jan-09 z pending

and goes on....

In sheet2 I want a report.
Col A Col B Col C
Weeks completed pending
Jan 1-2
Jan 5-9
Jan 12-16
Jan 19-23
Jan 26-30

in cell B2 below completed I use this formula ( Array Formula)

=COUNT(IF(MONTH(sheet1!$B$2:$B$500)=1,IF(DAY(sheet 1!$B$2:$B$500)=1,IF
(DAY(sheet1!$B$2:$B$500)<=2,IF(sheet1!$C$2:$C$500= sheet2!$B$1,)))))

it will give me the result as 2, for the First week of Jan,
For Second week i will change the days between 5 & 9.

Similarly i will do for the Feb, Mar,.......

Problem is that I personally feel that this is very big...
I guess we can use Pivot table ... by grouping the date , weekly data
we can get...

Any one knows better formula than the above.

And also I want how to convert the above formula to VBA ?

I used this formula
Application.worksheetfunction.count (iif(expre,true,false)......
same way the formula was created but it is giving error 13, type
mismatch
After analysing the problem - what I came know is Month function used
in vba only for single value.

Can we use what I have in formula ?

Hi Bob,

Thanks for the reply,

Assume i want to count the dates which falls under jan.

So, I will use =Count(if(month(A1:A10)=1,))
OR
=SUMPRODUCT(--(MONTH(A1:A10)=1))

How do I use this in VBA

is it application.worksheetfunction.count.............. or
application.worksheetfunction.sumproduct....




On Jan 13, 2:35*pm, "Bob Phillips" wrote:
=SUMPRODUCT(--(Sheet1!$B$2:$B$50<""),--(Sheet1!$D$2:$D$50=B$1),
--(MONTH(Sheet1!$B$2:$B$50)=MONTH(LEFT($A2,FIND("
",$A2)-1)&"-"&YEAR(TODAY()))),
--(DAY(Sheet1!$B$2:$B$50)--MID($A2,FIND(" ",$A2)+1,FIND("-",$A2)-FIND("
",$A2)-1)),
--(DAY(Sheet1!$B$2:$B$50)<=--MID($A2,FIND("-",$A2)+1,9)))

--
__________________________________
HTH

Bob

wrote in message

...



Hi All,

I have worksheet, which gives the date and status of the particular
task is completed or not.
sheet 1
Col A * Col B * * * * Col C * Col D
sl.no * *Date * * * * * *A/C * Status
1 * * * *02-jan-09 * * * *x * * *completed
2 * * * *02-jan-09 * * * *y * * *completed
3 * * * *02-jan-09 * * * *z * * *pending

and goes on....

In *sheet2 I want a report.
Col A * * * * * Col B * * * * *Col C
Weeks * * * *completed * pending
Jan 1-2
Jan 5-9
Jan 12-16
Jan 19-23
Jan 26-30

in cell B2 below completed I use this formula ( Array Formula)

=COUNT(IF(MONTH(sheet1!$B$2:$B$500)=1,IF(DAY(sheet 1!$B$2:$B$500)=1,IF
(DAY(sheet1!$B$2:$B$500)<=2,IF(sheet1!$C$2:$C$500= sheet2!$B$1,)))))

it will give me the result as 2, for the First week of Jan,
For Second week i will change the days between 5 & 9.

Similarly i will do for the Feb, Mar,.......

Problem is that I personally feel that this is very big...
I guess we can use Pivot table ... by grouping the date , weekly data
we can get...

Any one knows better formula than the above.

And also I want how to convert the above formula to VBA *?

I used this formula
Application.worksheetfunction.count (iif(expre,true,false)......
same way the formula was created but it is giving error 13, type
mismatch
After analysing the problem - what I came know is Month function used
in vba only for single value.

Can we use what I have in formula ?- Hide quoted text -

- Show quoted text -

Hi

Yes, it can be done simply with a Pivot Table if you drag Date to the Row
Area, Status to the Column Area and Status again to the Data area.
Then Group dates by Days7Starting at 30 Dec 2008.

If you want to do it by formula, then set up 3 columns on your sheet.
I used columns F,G and H.
In G1 enter Completed and in H1 enter Pending
In F2 enter 02 Jan 09
In F3 enter =F2+7 and copy down as far as required.

In G2 enter
=SUMPRODUCT(($B$2:$B$1000<=$F2)*($B$2:$B$1000$F1) *($D$2:$D$1000=G$1))
Copy across to H2, then copy G2:H2 down as far as required

Amend the ranges to suit your data, but ensure that ranges are of equal
length. Unless you are using Excel 2007, you cannot give a whole column as a
range.

You might find it easier to create Dynamic range names for the Data.
InsertNameDefine
Name Dates Refers to =$B$2:INDEX($B$B,COUNTA($B$B))
Name Status Refers to =$D$2:INDEX($D$D,COUNTA($B$B))

Note. Use Count on column B in each case to ensure ranges are of equal
length.
The formula then becomes
=SUMPRODUCT((Dates<=$F2)*(Dates$F1)*(Status=G$1))

--
Regards
Roger Govier

wrote in message
...
Hi All,

I have worksheet, which gives the date and status of the particular
task is completed or not.
sheet 1
Col A Col B Col C Col D
sl.no Date A/C Status
1 02-jan-09 x completed
2 02-jan-09 y completed
3 02-jan-09 z pending

and goes on....

In sheet2 I want a report.
Col A Col B Col C
Weeks completed pending
Jan 1-2
Jan 5-9
Jan 12-16
Jan 19-23
Jan 26-30

in cell B2 below completed I use this formula ( Array Formula)

=COUNT(IF(MONTH(sheet1!$B$2:$B$500)=1,IF(DAY(sheet 1!$B$2:$B$500)=1,IF
(DAY(sheet1!$B$2:$B$500)<=2,IF(sheet1!$C$2:$C$500= sheet2!$B$1,)))))

it will give me the result as 2, for the First week of Jan,
For Second week i will change the days between 5 & 9.

Similarly i will do for the Feb, Mar,.......

Problem is that I personally feel that this is very big...
I guess we can use Pivot table ... by grouping the date , weekly data
we can get...

Any one knows better formula than the above.

And also I want how to convert the above formula to VBA ?

I used this formula
Application.worksheetfunction.count (iif(expre,true,false)......
same way the formula was created but it is giving error 13, type
mismatch
After analysing the problem - what I came know is Month function used
in vba only for single value.

Can we use what I have in formula ?

This is worksheet formulae. If you want it in VBA, I would just write a
looping routine.

--
__________________________________
HTH

Bob

wrote in message
...
Hi Bob,

Thanks for the reply,

Assume i want to count the dates which falls under jan.

So, I will use =Count(if(month(A1:A10)=1,))
OR
=SUMPRODUCT(--(MONTH(A1:A10)=1))

How do I use this in VBA

is it application.worksheetfunction.count.............. or
application.worksheetfunction.sumproduct....




On Jan 13, 2:35 pm, "Bob Phillips" wrote:
=SUMPRODUCT(--(Sheet1!$B$2:$B$50<""),--(Sheet1!$D$2:$D$50=B$1),
--(MONTH(Sheet1!$B$2:$B$50)=MONTH(LEFT($A2,FIND("
",$A2)-1)&"-"&YEAR(TODAY()))),
--(DAY(Sheet1!$B$2:$B$50)--MID($A2,FIND(" ",$A2)+1,FIND("-",$A2)-FIND("
",$A2)-1)),
--(DAY(Sheet1!$B$2:$B$50)<=--MID($A2,FIND("-",$A2)+1,9)))

--
__________________________________
HTH

Bob

wrote in message

...



Hi All,

I have worksheet, which gives the date and status of the particular
task is completed or not.
sheet 1
Col A Col B Col C Col D
sl.no Date A/C Status
1 02-jan-09 x completed
2 02-jan-09 y completed
3 02-jan-09 z pending

and goes on....

In sheet2 I want a report.
Col A Col B Col C
Weeks completed pending
Jan 1-2
Jan 5-9
Jan 12-16
Jan 19-23
Jan 26-30

in cell B2 below completed I use this formula ( Array Formula)

=COUNT(IF(MONTH(sheet1!$B$2:$B$500)=1,IF(DAY(sheet 1!$B$2:$B$500)=1,IF
(DAY(sheet1!$B$2:$B$500)<=2,IF(sheet1!$C$2:$C$500= sheet2!$B$1,)))))

it will give me the result as 2, for the First week of Jan,
For Second week i will change the days between 5 & 9.

Similarly i will do for the Feb, Mar,.......

Problem is that I personally feel that this is very big...
I guess we can use Pivot table ... by grouping the date , weekly data
we can get...

Any one knows better formula than the above.

And also I want how to convert the above formula to VBA ?

I used this formula
Application.worksheetfunction.count (iif(expre,true,false)......
same way the formula was created but it is giving error 13, type
mismatch
After analysing the problem - what I came know is Month function used
in vba only for single value.

Can we use what I have in formula ?- Hide quoted text -

- Show quoted text -

Yes, I want that in a VBA

Thanks bob






On Jan 13, 3:46*pm, "Bob Phillips" wrote:
This is worksheet formulae. If you want it in VBA, I would just write a
looping routine.

--
__________________________________
HTH

Bob

wrote in message

...
Hi Bob,

Thanks for the reply,

Assume i want to count the dates which falls under jan.

So, I will use =Count(if(month(A1:A10)=1,))
* * * * * * * *OR
=SUMPRODUCT(--(MONTH(A1:A10)=1))

How do I use this in VBA

is it application.worksheetfunction.count.............. or
application.worksheetfunction.sumproduct....

On Jan 13, 2:35 pm, "Bob Phillips" wrote:



=SUMPRODUCT(--(Sheet1!$B$2:$B$50<""),--(Sheet1!$D$2:$D$50=B$1),
--(MONTH(Sheet1!$B$2:$B$50)=MONTH(LEFT($A2,FIND("
",$A2)-1)&"-"&YEAR(TODAY()))),
--(DAY(Sheet1!$B$2:$B$50)--MID($A2,FIND(" ",$A2)+1,FIND("-",$A2)-FIND("
",$A2)-1)),
--(DAY(Sheet1!$B$2:$B$50)<=--MID($A2,FIND("-",$A2)+1,9)))

--
__________________________________
HTH

Bob

wrote in message

...

Hi All,

I have worksheet, which gives the date and status of the particular
task is completed or not.
sheet 1
Col A Col B Col C Col D
sl.no Date A/C Status
1 02-jan-09 x completed
2 02-jan-09 y completed
3 02-jan-09 z pending

and goes on....

In sheet2 I want a report.
Col A Col B Col C
Weeks completed pending
Jan 1-2
Jan 5-9
Jan 12-16
Jan 19-23
Jan 26-30

in cell B2 below completed I use this formula ( Array Formula)

=COUNT(IF(MONTH(sheet1!$B$2:$B$500)=1,IF(DAY(sheet 1!$B$2:$B$500)=1,IF
(DAY(sheet1!$B$2:$B$500)<=2,IF(sheet1!$C$2:$C$500= sheet2!$B$1,)))))

it will give me the result as 2, for the First week of Jan,
For Second week i will change the days between 5 & 9.

Similarly i will do for the Feb, Mar,.......

Problem is that I personally feel that this is very big...
I guess we can use Pivot table ... by grouping the date , weekly data
we can get...

Any one knows better formula than the above.

And also I want how to convert the above formula to VBA ?

I used this formula
Application.worksheetfunction.count (iif(expre,true,false)......
same way the formula was created but it is giving error 13, type
mismatch
After analysing the problem - what I came know is Month function used
in vba only for single value.

Can we use what I have in formula ?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -