• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert monthly data to weeks

O

Oxidised

Member
Hi,
I have some budget data in months that I want to split up into weeks to do a forecast. Now the issue with months to weeks is that you can't just divide by 4 or 5 cos it changes. The only way I can think of doing it is to put in a helper row above the monthly data and the weekly data, then do a lookup of that helper row and divide by a countif of the number of weeks in the month. It is a bit clunky, but works OK.

Is there a better way of doing this? and yes, I have tried searching the forums and didn't find what I was after.
See file attached, thanks,

EDIT - the issue with this way is it doesn't account for weeks that go across the end of the month... It would be nice, but not essential, to proportion things per week across month end... maybe using the number of days in the month??
 

Attachments

  • months to weeks.xlsx
    10.1 KB · Views: 135
Last edited:
Hi, Oxidised!

The function WEEKNUM returns the week no. for a given date, related to the beginning of the year. Years have 365/366 days, that are 52*7+1/2, so all years have 53 or 54 weeks, depending on which day a week starts.

The first point to define is which is the 1st week of the year: if that of Jan 1st, if that of the 1st Monday, etc.

The second point to define is how do you want to split the data of weeks that overlap over the end of a month and the beginning of the next one: if proportionally, if considering which one has more days (workable or all?), etc. So months could have 4, 5 or 6 weeks depending on your definitions.

Regards!
 
Ah, yes, defining the week would be helpful! The week is ending Friday. The first week of the year will have the first Friday of the year in it.

I would split data over the monthend proportionally for all 7 days of the week... so it could be 2-5 or 3-4 etc...
 
Hi, Oxidised!
If you're going to split data proportionally then the 2nd week of the year will be that which starts on 1st Saturday and the 1st week will be the period Jan 1st thru 1st Friday. Unless Jan 1st is Friday where the 1st week will be complet. Am I wrong?
If it's Ok, then you'll have from 4 to 6 weeks per month, i.e., up to 72 weeks (some full, some partial).
Regards!
 
Ummm... sorry, I meant that it would be good to split the data proportionally, not the week. I really only want to have 53 weeks, each starting on Sat, finishing on a Friday.
But if there is a week that is say from 28th of one month to 3rd of the next month, then when I split the monthly data it should take the equivalent of 4 days proportion form the first month and 3 days proportion from the second month so the weekly total for that week looks something like:
= 4/31*X + 3/30*Y

and if the week is entirely within a single month, it should have the format
= 7/31*X

at the moment in my file I attached, all I am effectively doing is going =X/4 or =X/5 depending on how many weeks are counted as being part of that month.
 
Hi ,

I would like you to clarify / confirm the following :

1. The month always starts on the first of that month i.e. the first week of January will start on January 1 , irrespective of whether that is a Saturday or not.

2. The week always ends on a Friday ; so what will happen if the first of any month falls on a Friday ?

3. You cannot combine splitting months into weeks , and then try to account for a partial week at the end of the month in terms of days. For instance , suppose we take January 1 through January 7 as week 1 ( as you have done in your workbook ) , January 8 through January 14 as week 2 ,... Thus we have January 29 through February 4 as week 5.

Now , if the data for January is 30 , if we divide 30 by 4 , we get 7.5 , which means we cannot apportion any of the 30 to the days 29 through 31 ; if we divide 30 by 5 , then we get 6 for each week ; even though the 5th week has only 3 days from January in it , we still need to allot the 6 to these 3 days , since otherwise we will not get 30 if we add all the weeks of January.

Narayan
 
Hi, Oxidised!

Give a look at the uploaded file. It's the same as yours but without the unnecessary count rows and the new calculations.

It uses 3 dynamic named ranges for easily referencing:
DataTable: rows 1:2 from column B until last one (in the sample column N)
MonthList: 1st row ot DataTable
DataList: 2nd row of DataTable

Output is in rows 4:6 (week start, week end, new data), from column B until last colored cell in row 4 (in the sample column BH)
1st week start, B4: =B1-SI(DIASEM(B1)<7;7;0)+7-DIASEM(B1) -----> in english: =B1-IF(WEEKDAY(B1)<7,7,0)+7-WEEKDAY(B1)
week start, C4: =SI(B4+1<=FIN.MES(MAX(1:1);0);B4+7;"") -----> in english: =IF(B4+1<=EOMONTH(MAX(1:1),0),B4+7,"")
week end, B5: =SI(B4="";"";B4+6) =IF(B4="","",B4+6)

In rows 7:10 there're helper rows to build the new data formula in row 11:
B11: =SI(B4="";"";SI(B10=0;0;INDICE(DataList;B10))*B10/DIA(FIN.MES(B4;0))+SI.ERROR(SI(A10=0;0;INDICE(DataList;A10))*(7-A10)/DIA(FIN.MES(A4;0));0)) -----> in english: =IF(B4="","",IF(B10=0,0,INDEX(DataList,B10))*B10/DAY(EOMONTH(B4,0))+IFERROR(IF(A10=0,0,INDEX(DataList,A10))*(7-A10)/DAY(EOMONTH(A4,0)),0))

And if you want to get rid of the helper columns, then here you have the "new data insane" formula at row 12 (if you don't want to, and I suggest so, you could hide them):
B12: =SI(B4="";"";SI(SI.ERROR(COINCIDIR(B4+0,01;MonthList;1);0)=0;0;INDICE(DataList;SI.ERROR(COINCIDIR(B4+0,01;MonthList;1);0)))*SI.ERROR(COINCIDIR(B4+0,01;MonthList;1);0)/DIA(FIN.MES(B4;0))+SI.ERROR(SI(SI.ERROR(COINCIDIR(A4+0,01;MonthList;1);0)=0;0;INDICE(DataList;SI.ERROR(COINCIDIR(A4+0,01;MonthList;1);0)))*(7-SI.ERROR(COINCIDIR(A4+0,01;MonthList;1);0))/DIA(FIN.MES(A4;0));0)) -----> in english: =IF(B4="","",IF(IFERROR(MATCH(B4+0.01,MonthList,1),0)=0,0,INDEX(DataList,IFERROR(MATCH(B4+0.01,MonthList,1),0)))*IFERROR(MATCH(B4+0.01,MonthList,1),0)/DAY(EOMONTH(B4,0))+IFERROR(IF(IFERROR(MATCH(A4+0.01,MonthList,1),0)=0,0,INDEX(DataList,IFERROR(MATCH(A4+0.01,MonthList,1),0)))*(7-IFERROR(MATCH(A4+0.01,MonthList,1),0))/DAY(EOMONTH(A4,0)),0))

Just advise if any issue.

Regards!
 

Attachments

  • convert monthly data to weeks (for Oxidisxed at chandoo.org).xlsx
    17.7 KB · Views: 157
Dear SirJB7 and Oxidised

I think that there is clearly an issue with SirJB7's formulas since C14 is calculating a week entirely within January yet coming up with a value which only has 1 day's worth of January. Similarly G14 is incorrect.

Standing on SirJB7's shoulders somewhat, I find that the following formula works (in place of the new data insane formula):

=IF(MONTH(B6)=MONTH(B7),IFERROR(INDEX(DataList,MATCH(B6+0.01,MonthList,1)),0)*7/DAY(EOMONTH(B6,0)),(7-DAY(B7))*IFERROR(INDEX(DataList,MATCH(B6+0.01,MonthList,1)),0)/DAY(EOMONTH(B6,0))+DAY(B7)*IFERROR(INDEX(DataList,MATCH(B7+0.01,MonthList,1)),0)/DAY(EOMONTH(B7,0)))

Sorry I can't translate into Spanish.
 
Hi, Jake Collins!
C14? Formulas go up to row 12. If it's about C12, with a value of .9677 in the detailed formula at C11 it has an error in the part of "*C10" where it should be "*C8", at least. Error which has been propagated to C12 formula, of course.
Thanks for the catch, I'm leaving now so I can't revise it now nor check your posted formula. Hope to be doing it tomorrow.
Regards!
 
Last edited:
Wow, just wow. Thanks so much for that. Think I need to go get a hot drink and sit down to examine how that works!
I have copied in Jake's amended forumula to a new line (and made sure it reference the correct week start/end dates) and it seems to give results that make sense.

Who would have though going from months to weeks was so complicated, but SirJB7, your interpretation of how I wanted to split up the data was indeed correct.
 
NARAYANK991 said:
Hi ,

I would like you to clarify / confirm the following :

1. The month always starts on the first of that month i.e. the first week of January will start on January 1 , irrespective of whether that is a Saturday or not.

2. The week always ends on a Friday ; so what will happen if the first of any month falls on a Friday ?

3. You cannot combine splitting months into weeks , and then try to account for a partial week at the end of the month in terms of days. For instance , suppose we take January 1 through January 7 as week 1 ( as you have done in your workbook ) , January 8 through January 14 as week 2 ,... Thus we have January 29 through February 4 as week 5.

Now , if the data for January is 30 , if we divide 30 by 4 , we get 7.5 , which means we cannot apportion any of the 30 to the days 29 through 31 ; if we divide 30 by 5 , then we get 6 for each week ; even though the 5th week has only 3 days from January in it , we still need to allot the 6 to these 3 days , since otherwise we will not get 30 if we add all the weeks of January.

Narayan
Click to expand...

In answer to Narayank991's questions
1. week should always go from Sat to Friday. (why financial people do this is beyond my engineering brain!). The first day of the month is not relevant in deciding which is the first day of the week, only in determining what proportion of the monthly data is apportioned to each week.

2. If the first day of the month is a Friday, that Friday sill belongs to the previous week, which will be made up of 6 days of the previous month, and 1 day of the new month... so in that specific example, where say the Friday was the first of March, the formula for that week should resolve to being of the format: = 6/28*X + 1/31*Y where X is Feb total and Y is March total.

3. The issue of dividing the monthly total by the number of weeks and allocating to each week is the exact issue I had and the reason I posted this thread, you just explained it more clearly than I did! As SirJB7 has done, the solution is to divide the month into days, then allocate the appropriate number of days of each month to the corresponding week.

I just could not wrap my head around doing that easily... and given the solution presented above I'm not surprised I could not work it out!! Thanks again guys.
 
Hi ,

If I have understood you correctly , the entire calculation starts from the first Saturday of the year ; after this , it is just a daily calculation.

Given that you have split up your data into amounts per month , we can easily split up these amounts into per day amounts for each month ; depending on the monthly amounts , the per day amount will be different for different months.

I really do not think that the formula needs to be so complicated , if you are interested , I can work out a solution , which may involve helper cells.

Narayan
 
Hi Narayank. The solution started by SirBJ using Jakes corrected formula does indeed work and achieve the output I desired. If you believe there is an easier way to achieve the same result I would be interested to see that as an option.
Attached is the latest sheet which includes Jakes formula in blue.
 

Attachments

  • convert monthly data to weeks (for Oxidisxed at chandoo.org).xlsx
    17 KB · Views: 119
Hi ,

See this file.

My intention was not to show an easier way of doing anything ; my point was to understand your problem. I believe that if a problem is simple , the solution is rarely complicated , though there may always be complicated ways of solving simple problems ; I believe the term for this is a Rube Goldberg machine. As a problem solver , my first effort is to understand the problem as clearly as I can ; only when we have understood a problem should we look for a solution , and in looking for solutions , I always strive to look for simpler solutions , even if it means using helper cells / columns.

Just BTW , the formula does not need any helper cells beyond the Daily_Rates row.

Narayan
 

Attachments

  • Oxidised_Example.xlsx
    17.2 KB · Views: 107
It is certainly wise to ensure the problem is understood correctly! makes a big difference to the shape of the solution.

I really like the daily_rates row idea! thanks.

Just for kicks, I redid the formula so it has the daily rates calc buried in it so there is no helper rows at all. Only thing it achieves is makes the formula long, but in the model that this is going to be used in, there are lots of rows already with all sorts of stuff, and reducing the number of helper rows is beneficial.

=IF(MONTH(B5)=MONTH(B6),(B6-B5+1)*IFERROR(INDEX(DataTable,2,MATCH(EOMONTH(B5,-1)+1,MonthList,0)),0)/DAY(EOMONTH(B5,0)),(EOMONTH(B5,0)-B5+1)*IFERROR(INDEX(DataTable,2,MATCH(EOMONTH(B5,-1)+1,MonthList,0)),0)/DAY(EOMONTH(B5,0))+(B6-EOMONTH(B6,-1))*IFERROR(INDEX(DataTable,2,MATCH(EOMONTH(B6,-1)+1,MonthList,0))/DAY(EOMONTH(C5,0)),0))

So basically IF (month of the week start and end date are equal, THEN (end date - start date+1)*monthly total/days in month , ELSE (end of previous month - start date) * previous monthly total/days in previous month + (end date - start of month) * monthly total/days in month)

Looks a bit simpler when I write it out like that. Should try that more often...

The dynamic named range is a learning, the daily rate was a great idea and now my model will be much more accurate! thanks all!
 
Hi ,

The only change I would suggest is that if the month of the week start date and the month of the week end date are the same , then ( end date - start date + 1 ) will always equal 7 ; much better to use 7 itself.

In the ELSE portion , in addition to subtracting , you will have to add 1 to each of the terms :

(end of previous month - start date) becomes (end of previous month - start date + 1)

(end date - start of month) becomes (end date - start of month + 1)

To make things a little more simple , since the start of month will always equal 1 , the term :

(end date - start of month + 1) becomes end date

Of course , the dates mentioned here are really days from 1 through 28-31 , which is why the DAY function has been used.

Narayan
 
Hi, Oxidised!

Give a look to the uploaded file. It has fixed the typo errors. These are the changed formulas:

formula with helpers, B11: =SI(B4="";"";SI(B10=0;0;INDICE(DataList;B10))*B8/DIA(FIN.MES(B4;0))+SI.ERROR(INDICE(DataList;B10)*(7-A8)/DIA(FIN.MES(B4;0));0)) -----> in english: =IF(B4="","",IF(B10=0,0,INDEX(DataList,B10))*B8/DAY(EOMONTH(B4,0))+IFERROR(INDEX(DataList,B10)*(7-A8)/DAY(EOMONTH(B4,0)),0))

insane formula, B12: =SI(B4="";"";SI(SI(B4="";"";SI.ERROR(COINCIDIR(B4+0,01;MonthList;1);0))=0;0;INDICE(DataList;SI(B4="";"";SI.ERROR(COINCIDIR(B4+0,01;MonthList;1);0))))*SI(B4="";"";7-SI(MES(B4)=MES(B5);0;DIA(B5)))/DIA(FIN.MES(B4;0))+SI.ERROR(INDICE(DataList;SI(B4="";"";SI.ERROR(COINCIDIR(B4+0,01;MonthList;1);0)))*(7-SI(A4="";"";7-SI(MES(A4)=MES(A5);0;DIA(A5))))/DIA(FIN.MES(B4;0));0)) -----> in english: =IF(B4="","",IF(IF(B4="","",IFERROR(MATCH(B4+0.01,MonthList,1),0))=0,0,INDEX(DataList,IF(B4="","",IFERROR(MATCH(B4+0.01,MonthList,1),0))))*IF(B4="","",7-IF(MONTH(B4)=MONTH(B5),0,DAY(B5)))/DAY(EOMONTH(B4,0))+IFERROR(INDEX(DataList,IF(B4="","",IFERROR(MATCH(B4+0.01,MonthList,1),0)))*(7-IF(A4="","",7-IF(MONTH(A4)=MONTH(A5),0,DAY(A5))))/DAY(EOMONTH(B4,0)),0))

Regards!
 

Attachments

  • convert monthly data to weeks (for Oxidisxed at chandoo.org).xlsx
    17.7 KB · Views: 236
Hi, Oxidised!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Hi Guys,

I have had the exact same query has Oxidised has had in getting a monthly budget into weeks. Glad to see it's not just me!

I think with the final combined formula of Jake Collins and SirJB7 that Oxidised has made available will give me exactly what I require, although I do not understand the main formula itself. I also see Narayank991 has done it a slightly different way. Great work all!!

My budget is for 2016 so I have adapted the dates accordingly in the formula to pick up the start of the overlapping week from Dec 2015 to Jan 16. This has filtered through nicely.

What I would like some advice on is how to replicate the formula for the next row and subsequent rows thereafter i.e. I have numerous more rows of data (not just row 2) and would like to in essence drag the formula down to replicate for the additional rows data.

I have tried to fix certain cells and drag down but as I don't understand the formula itself I can't see how it is actually picking up the data in row2 and therefore how it would pick up the data in subsequent rows.

Any help on this as soon as possible will be much appreciated

Thanks a lot
 
Last edited:
You must log in or register to reply here.
Back
Top