Converting Monthly data to Weekly data in Excel

A lot of the time, there are differences between different pieces of data received from a client/ data team – one being when different pieces roll up to different levels in a nested structure, for eg. weekly vs monthly vs yearly. Is that problem? Let’s take a look..

Taking the example here – let’s assume we have Monthly data for spends for campaigns while we’re working at a weekly level with Sales. Thus, we’re forced to split Monthly figures into the Weekly format to be used for any purposes – from reporting to modeling. It is often required to accurately split the monthly into weekly, so as not to double count or misrepresent the spends in the weekly format. One of the biggest problems – overlapping days between the week and months.

For eg. from the image below – the week of 27th Jan 2014 contains days from Jan ’14 as well as Feb ’14. In order to accurately get the spends for this week, we need to factor Jan & Feb spends weighted by number of days.Excel Monthly-Weekly

Here’s a neat trick that solves the problem with one formula – that’s right, just ONE formula!

=IF(C$2<$B3,MAX(0,C$2-EOMONTH($B3,-1)+6),MAX(0,MIN(7,EOMONTH($B3,0)-C$2+1)))/(EOMONTH($B3,0)-$B3+1)

And you get this –Blog - Excel v1
Blog - Excel

 

 

 

Credits to this guy – “AlphaFrog”. His post (http://www.mrexcel.com/forum/excel-questions/627101-converting-data-monthly-weekly.html) provided the basis for this post. Below is a quick snapshot from his page showing the flow of logic.Example - Blog

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s