asked on # Splitting date range into weekly buckets

i have the following data

Daily Avg

04/09/2009

22/10/2009 26.34029356

27/01/2010 94.54060731

23/04/2010 83.35397306

21/07/2010 19.88248141

16/08/2010 14.78855542

17/08/2010 0

25/10/2010 24.35493734

28/01/2011 101.1063237

20/04/2011 66.19501607

i.e. 4-Sep-2009 to 22-Nov-2009 the daily average was 26.34...

what i need is to split this into 52 weekly buckets per year, is this something i can do with Excel functions or does it need VBA programming

Daily Avg

04/09/2009

22/10/2009 26.34029356

27/01/2010 94.54060731

23/04/2010 83.35397306

21/07/2010 19.88248141

16/08/2010 14.78855542

17/08/2010 0

25/10/2010 24.35493734

28/01/2011 101.1063237

20/04/2011 66.19501607

i.e. 4-Sep-2009 to 22-Nov-2009 the daily average was 26.34...

what i need is to split this into 52 weekly buckets per year, is this something i can do with Excel functions or does it need VBA programming

Microsoft ExcelMicrosoft Office

Yup each week during the period would be equal to 26.34.

i tried creating a table of 52 entries with week beggining dates in the first column and this in the second =VLOOKUP(B58,data,2,TRUE) but it doesnt quite cut it.

i tried creating a table of 52 entries with week beggining dates in the first column and this in the second =VLOOKUP(B58,data,2,TRUE) but it doesnt quite cut it.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Hey @teylyn, that works a treat (i knew it should be that easy, but couldnt get it to gel)

Only issue is that it doesnt stop when the data stops and it fills in the rest of 2011 with the 66.195 any ideas on that?

Only issue is that it doesnt stop when the data stops and it fills in the rest of 2011 with the 66.195 any ideas on that?

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Hi,

You can use the function WeekNum if you have the 'Analysis Toolpak' add-in ticked on, (menu > Tools > Add-ins). This add-in is standard Microsoft but is not enabled by default on installation.

2 parameters, first is the date which is required, and the second, which is optional, determines if the week starts on a Sunday (value is 1) or a Monday (value is 2); default is Sunday.

For example: If cell A1 has yesterdays date in it (07-Aug-2011, i.e. Sunday) you get the following:

=weeknum(A1) - result is: 33 <-- default of 1 is applied and week starts on Sunday.

=weeknum(A1,1) - result is: 33

=weeknum(A1,2) - result is: 32 <--Week starts on Monday, i.e. Sunday is the last day of the previous week

Hope this helps.

Nero

You can use the function WeekNum if you have the 'Analysis Toolpak' add-in ticked on, (menu > Tools > Add-ins). This add-in is standard Microsoft but is not enabled by default on installation.

2 parameters, first is the date which is required, and the second, which is optional, determines if the week starts on a Sunday (value is 1) or a Monday (value is 2); default is Sunday.

For example: If cell A1 has yesterdays date in it (07-Aug-2011, i.e. Sunday) you get the following:

=weeknum(A1) - result is: 33 <-- default of 1 is applied and week starts on Sunday.

=weeknum(A1,1) - result is: 33

=weeknum(A1,2) - result is: 32 <--Week starts on Monday, i.e. Sunday is the last day of the previous week

Hope this helps.

Nero

Good solution, easy to follow, Excellent response.

regards, barry