What week numbers are in a specific month

[apt147@hotmail.com]

Hi all,

I'm new to this forum and in a little bit of a bind with an excel report I need to create.

I am receiving automatically weekly data into an excel spreadsheet (i.e. 1-52). This weekly data I now need to group by month (i.e. 1-12). Ideally I could use a PivotTable to do this but the software I'm using (which has excel embedded) doesn't allow PivotTables.

Is there any other way to group the weekly data into month? I'm conscious that each year will have a varying number of weeks in a month so any help which you can give would be appreciated.

Thanks in advance

[Fotis1991]

How your data look like? Are there any dates in any column?

[apt147@hotmail.com]

Yes. I'm getting a timestamp column and data column. The raw data which I have is coming with a timestamp of yyyy-mm-dd hh:mm

[Fotis1991]

In this case, perhaps you can use month function for getting the month of the date..

=Month(a1)

..and then use this information to create your report..using INDEX & MATCH functions...

But i'll be able to help you more if you upload a small sample workbook...

Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[apt147@hotmail.com]

Sure no problem.

Essentially I converted the timestamp to week number using the function WeekNum. From here I have data from week 1 to (currently) week 25. What I'm looking for is a way to automatically populate the month (i.e. Month 1) with the number of weeks within that month. Manually it's not an issue as I can do through calendar and identify how many weeks are in a specific month but I'd like to automate it so any given month will know exactly the required number of weeks to sum.

I hope this makes sense.

[oeldere]

2013 4 309.063

What value belongs to januar and what value belongs to februar?

[Fotis1991]

We can use the Timestamp column to use in our formula.

=SUMPRODUCT((MONTH($A$2:$A$1000)=F3)*($D$2:$D$1000))

[Rambo4711]

I dont think there is any possibilty to make a formula to based only on Year and Weeknumbers to calculate this.

I suggest that you manually insert a new column C called "Month" and for each week put in the month number that you want to allocate that week to.

Then you can do this formula in cell G2 (old F3):

=SUMIF(C:C,F3,D:D)

Copy the formula down