SUMPRODUCT to count by week

[sanlen]

Hi,

Hi,

I am using Excel 2003. I need to track the jobs that have done and count them on the weekly basis. For example, i have the list of date that completed task as below:

1-Aug-2010
9-Jan-2010
6-Jun-2010
1-Feb-2010
15-Feb-2010
15-Feb-2010
21-Jun-2010
14-Jul-2010
2-Mar-2010
3-Feb-2010
1-Jun-2010
1-Jan-2010
1-Apr-2010
1-May-2010
1-May-2010
1-Mar-2010
1-Mar-2010

I want to count them and put in the list as below:

Weeknumber: 1 2 3 4 5 6 7 8 9 10 11 ......
Num Of job done:

Could you please advice how can i get this done?

Please apologize if the question is not clear enough.

Best Regards,
sanlen

[xenixman]

If I understand you correctly, you want the Week Number that the job was finished?

With your data starting in Cell 1, Column A

Put this formula in Cell 1, Column B

=INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)

drag down the number of dates you have.

Your example data should look like:

    A         B
 1-Aug-10    30
 9-Jan-10     1
 6-Jun-10    22
 1-Feb-10     5
15-Feb-10     7
15-Feb-10     7
21-Jun-10    25
14-Jul-10    28
 2-Mar-10     9
 3-Feb-10     5
 1-Jun-10    22
 1-Jan-10    53
 1-Apr-10    13
 1-May-10    17
 1-May-10    17
 1-Mar-10     9
 1-Mar-10     9 

Hopefully, this is what you want.

[zbor]

Or this: =WEEKNUM(A1,2)-1

[sanlen]

Hi,

Thank you very much for your help. I now attached an excel file for your reference.

I have the DATE COMPLETED whis is the date that the job done. WEEK NUMBER is the the number of the week that the specific job completed.

I want to count how many jobs that have completed in week number 1, how many jobs that have completed in week number 2, number 3,.....

In my real situation, i am not supposed to convert the DATE COMPLETED to WEEK NUMBER. I want to get the value direct from DATE COMPLETED.

Thank you very much for your time and any further advice you may give me.

sanlen

[jawad1_ali]

Hi,

check the attachement please. Is this what u were looking for?

[DonkeyOte]

In my real situation, i am not supposed to convert the DATE COMPLETED to WEEK NUMBER. I want to get the value direct from DATE COMPLETED.

sanlen, you have Mon 4th Jan denoted as Week 1 - on that basis can we ask the week number you would assign to 28-Dec-2009 to 03-Jan-2010 ?

[sanlen]

Hi,

check the attachement please. Is this what u were looking for?

Thank you very much.

But can i count just base on the date? i mean i dont want to convert those date to week number and take off that column.

Thank you very much.

sanlen

[sanlen]

sanlen, you have Mon 4th Jan denoted as Week 1 - on that basis can we ask the week number you would assign to 28-Dec-2009 to 03-Jan-2010 ?

Actually, the completion date start from 1/Jan/2010.

Thanks

sanlen

[jawad1_ali]

sanlen, i am not sure, if that is possible with functions..

We can do that with macro, but I would suggest, it doesn't worth writing macro for this, I think, adding extra columns for each date column and then hiding is not bad either.

[jawad1_ali]

You can protect the sheet if you dont' want your users to see it

[sanlen]

Hi All,

i was thinking of doing so (adding another columns and then count it). However, i have around 50 columns (milestones) to count

Please help...

Best Regards,
sanlen

[DonkeyOte]

sanlen, you have Mon 4th Jan denoted as Week 1 - on that basis can we ask the week number you would assign to 28-Dec-2009 to 03-Jan-2010 ?

Actually, the completion date start from 1/Jan/2010.

This doesn't really answer my question...

Put another way... what are the week numbers of the following dates:

1 Jan 2010
3 Jan 2010
4 Jan 2010
9 Jan 2010
11 Jan 2010

Until we understand the logic to be used in determining week no. any solution offered will based on guesswork which is generally ill advised (for all parties concerned).

[sanlen]

This doesn't really answer my question...

Put another way... what are the week numbers of the following dates:

1 Jan 2010
3 Jan 2010
4 Jan 2010
9 Jan 2010
11 Jan 2010

Until we understand the logic to be used in determining week no. any solution offered will based on guesswork which is generally ill advised (for all parties concerned).

If we use WEEKNUM then the value should be:

1-Jan-10 1
3-Jan-10 2
4-Jan-10 2
9-Jan-10 2
11-Jan-10 3

Best Regards,
sanlen

[jawad1_ali]

hey Sanlen,

check this one..calculating the same without weeknum..