| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate sales in the week by day ? | Excel Worksheet Functions | |||
| Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
| Macro to insert copied cells | Excel Discussion (Misc queries) | |||
| I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) | |||
| Convert week number into calendar month? | Excel Worksheet Functions | |||
Linear Mode
