Converting Weekly Data into Months.

**BayEnder111** · 11-25-2014, 11:11 AM

Hi All,

I have a long list of data split by week(A1) with the relevant total in the next cell (B1):

29/04/2013 74
06/05/2013 31
13/05/2013 53
20/05/2013 69
27/05/2013 63
03/06/2013 41
10/06/2013 42
17/06/2013 67
24/06/2013 54
01/07/2013 98
08/07/2013 44
15/07/2013 55

The issue I've got is that I want to split the data into Month's however some weeks overlap between months.

Is there any sort of formula that would split the week down into 7 days and the be able to work out how many days in that week were in the relevant month?

I can't really think of another way to do it.

Any help greatly appreciated as always.

Thanks.

**igoodable** · 11-25-2014, 11:40 AM

Can you give an example of the problem and desire solution? I'm not understanding your issue.

**miloki** · 11-25-2014, 11:45 AM

Morning BayEnder.

Trying to manipulate your data I ran across as issue with the dates. I notice you are in UK, and your standard date format is likely different from mine, but in my excel those dates do not match up well and wont work correctly.

You will need to ensure that the values in column A are actual dates, and are formatted appropriatley. You can do this easily by setting the format of the cells as date, then add a sort/filter to that column. When you attempt to sort the column if you see the year (2013) and all the months with little plus buttons next to them then you are good. If you see any dates listed independantly and exactly as they appear in the xl cells then you have misformatted dates.

Your data in my XL sheet does this, and attempts to use the day value (if <12) as the month value. For all others, it counts them as plain text. As a UK user, I believe your dates are a supported format and should work out, but please ensure that they are all counted as true dates. Then, see below:

Place this formula in the adjacent cell and drag down.

Formula:

Please Login or Register  to view this content.

You will get a single digit value that is 7 or less. This value should indicate the # of days in that week that lie within the specified month.

Cheers

Happy Excelling

Miloki

**Chris 53** · 11-25-2014, 11:56 AM

I've put your data in A2:b13. I've also entered in a1 the previous week's date. This formula I've put into C2 and copied down.

=IF(A1="",B2,IF(MONTH(A2)=MONTH(A1),C1+B2+IF(MONTH(A2)=MONTH(A3),0,B3-DAY(A3)/7*B3),B2*DAY(A2)/7))

Then in Column D I've put :-

=IF(MONTH(A2)<>MONTH(A3),C2,D3)

This gives you the month's totals.

Watchout for the formatting, may be formatted as a date when it should be a number and there will be a lot of decimal places, but you can sort that with formatting

Does this help?

Chris

**BayEnder111** · 11-26-2014, 07:16 AM

Originally Posted by Chris 53

I've put your data in A2:b13. I've also entered in a1 the previous week's date. This formula I've put into C2 and copied down.

=IF(A1="",B2,IF(MONTH(A2)=MONTH(A1),C1+B2+IF(MONTH(A2)=MONTH(A3),0,B3-DAY(A3)/7*B3),B2*DAY(A2)/7))

Then in Column D I've put :-

=IF(MONTH(A2)<>MONTH(A3),C2,D3)

This gives you the month's totals.

Watchout for the formatting, may be formatted as a date when it should be a number and there will be a lot of decimal places, but you can sort that with formatting

Does this help?

Chris

Thanks for this Chris - that really helps.

The only issue I'm having is on the month cutovers. E.g. the date is week commencing but for 06/05/13 (Weekly Total 48) the monthly cumulative total is displaying as 41.

It should be 48 as it's from 06/05/13 - 12/05/13.

The above probably looks like mindless rambling, I've attached a spreadsheet with the formulas in etc which might show it better.

Cheers.