Weekly > Monthly Portioning Problem

Hi all,

In a bit of a pickle. Basically I am trying to find a way to convert my dataset from a weekly format into a monthly format, however am having issues with the fact that my data goes on a week commencing basis (take for example wc. 30/12/19 - I only want 2/7ths of my data to fall into a Dec-19 range and 5/7ths into a Jan-20 range).

I have attached a test file which illustrates what I am working with - values by region.

Any help anyone could offer would be greatly appreciated (please no pivot-table 'group by' instructions - it doesn't work for the purposes of this I don't think).

Best,
C

You haven't provided an AFTER sheet in the workbook, so we have no way of seeing what you want.

Using the same value in every cell is not a good idea - it doesn't help us when testing possible solutions.

Sorry, Chris, but the sample workbook is pants.

Thanks.

You have included formulae - are these producing the correct results? If not, what should the results be (manually entered)? If they ARE correct, what is the problem?

OK - thanks. That makes sense now.

So how do the months divvy up? Is the 'end of the month' always the 19th of the month? If not, we shall need to know where that division comes for each month.

I got it from your opening post:

I only want 2/7ths of my data to fall into a Dec-19 range and 5/7ths into a Jan-20 range).

If your helper is unclear of the parameters involved, then working on a solution might prove a waste of their time. They will need to know how you are defining the 5/7ths.

No, I am not at all disgruntled!!! I am here to hep you to provide the information necessary for your helper (whoever thinks they can help you solve this - this could be me or somebody else) to be able to provide a solution.

I am merely trying to tease out of you the necessary parameters for that solution. At the moment, I am not clear about how you decide exactly what 5/7ths of a month should be.

To be clear: I would very much like to help you solve this, but I don't yet have the information I feel I need to do so. Does this make sense?

Yes, you are! But we are not there quite yet ...

The thing is, when we write a formula or some other solution, we need to tell Excel what the parameters are. You said this:

I figured out the 5/7th's by looking at the hypothetical week commencing in question (so say the 30th December 2019)... because I know that there are only 2 days left in December,

What I am driving at is that Excel will need to know how to do this and apply it to any future situation like this that may arise. Does this make sense? Excel is clever, but it can't see inside our heads.

I should let you know that I am going offline for a while shortly, but there are lots of others around who will be able to pick this up.

OK, so did you download the sample file?

Here's what I would do (if I were you):

1. Grab the sample file from that link.
2. Add some of your own data and see if it is calculating in the way you want.
3. If not, post the new workbook back here explaining what you would like to tweak.

By the way, 10/10 for Googling and finding that - big help.

I'm off to enjoy the sunshine now - my garden has been beckoning for the past hour or two! If you are working up in Manchester today, I hope your office is air-conditioned - it's 27C inside the house here at the moment!!!

No problem, Chris - I am sorry I gave the impression that I was getting cross with you: not intended at all.

Let us know how you get on, and if you are happy with the solution you've found, then please select Thread Tools from the menu link above and mark this thread as SOLVED.

Sometimes talking it through is wll we need!