group sales by week and week number
 

Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you

group sales by week and week number
 

Assuming your date is in B6, run this formula down a helper column
=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7) for the absolute week number.

Then either create a pivot table or use the SUBTOTAL function to subtotal by
week number.

If you want more info on the formula above, see this:
http://www.cpearson.com/excel/weeknum.htm

Dave
--
Brevity is the soul of wit.


"Wanna Learn" wrote:

Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you

group sales by week and week number
 

BTW, if you have the analysis toolpak installed, you can use the built-in
function WEEKNUM to return the week number of a date.
--
Brevity is the soul of wit.


"Dave F" wrote:

Assuming your date is in B6, run this formula down a helper column
=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7) for the absolute week number.

Then either create a pivot table or use the SUBTOTAL function to subtotal by
week number.

If you want more info on the formula above, see this:
http://www.cpearson.com/excel/weeknum.htm

Dave
--
Brevity is the soul of wit.


"Wanna Learn" wrote:

Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you

group sales by week and week number
 

THanks Dave Question I have the analysys tool pak however when I entered
the date 12 31 05 the results is week 53?

"Dave F" wrote:

BTW, if you have the analysis toolpak installed, you can use the built-in
function WEEKNUM to return the week number of a date.
--
Brevity is the soul of wit.


"Dave F" wrote:

Assuming your date is in B6, run this formula down a helper column
=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7) for the absolute week number.

Then either create a pivot table or use the SUBTOTAL function to subtotal by
week number.

If you want more info on the formula above, see this:
http://www.cpearson.com/excel/weeknum.htm

Dave
--
Brevity is the soul of wit.


"Wanna Learn" wrote:

Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you

group sales by week and week number
 

7 doesn't divide into 365 evenly....365/7 = 52.14285714, so the last day or
two of the year is the 53rd week.

Dave
--
Brevity is the soul of wit.


"Wanna Learn" wrote:

THanks Dave Question I have the analysys tool pak however when I entered
the date 12 31 05 the results is week 53?

"Dave F" wrote:

BTW, if you have the analysis toolpak installed, you can use the built-in
function WEEKNUM to return the week number of a date.
--
Brevity is the soul of wit.


"Dave F" wrote:

Assuming your date is in B6, run this formula down a helper column
=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7) for the absolute week number.

Then either create a pivot table or use the SUBTOTAL function to subtotal by
week number.

If you want more info on the formula above, see this:
http://www.cpearson.com/excel/weeknum.htm

Dave
--
Brevity is the soul of wit.


"Wanna Learn" wrote:

Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you

group sales by week and week number
 

Hi Wanna Learn

If you want to filter on weeks then look at EasyFilter
http://www.rondebruin.nl/easyfilter.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Wanna Learn" wrote in message ...
Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you

group sales by week and week number
 

Dave Now I understand. Here is the formula I'm using WEEKNUM(AE3-7) and it
seems to give me the correct week number. for example 11 08 -05 should be
week 45, but if I don't use -7 gives week 46 . should I continue to use this
formula

"Dave F" wrote:

7 doesn't divide into 365 evenly....365/7 = 52.14285714, so the last day or
two of the year is the 53rd week.

Dave
--
Brevity is the soul of wit.


"Wanna Learn" wrote:

THanks Dave Question I have the analysys tool pak however when I entered
the date 12 31 05 the results is week 53?

"Dave F" wrote:

BTW, if you have the analysis toolpak installed, you can use the built-in
function WEEKNUM to return the week number of a date.
--
Brevity is the soul of wit.


"Dave F" wrote:

Assuming your date is in B6, run this formula down a helper column
=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7) for the absolute week number.

Then either create a pivot table or use the SUBTOTAL function to subtotal by
week number.

If you want more info on the formula above, see this:
http://www.cpearson.com/excel/weeknum.htm

Dave
--
Brevity is the soul of wit.


"Wanna Learn" wrote:

Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you

group sales by week and week number
 

Ron thank you I do have the Easy Filter.......great tool!

"Ron de Bruin" wrote:

Hi Wanna Learn

If you want to filter on weeks then look at EasyFilter
http://www.rondebruin.nl/easyfilter.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Wanna Learn" wrote in message ...
Hello I have a large spreadsheet over 10 thousand lines ... How do I
group the sales by weeks , and I also need the week number of the year.
example I want to group all the sales for Nov 7 2005 to Nov 13 then what
is the week number for Nov 7 2005 thru Nov 13 2005 thank you