IF With Dates not adding up

[eyeope]

Hi,

I've attached a spreadsheet with a number of formulas.

What I'm trying to do, is divide the Amount (column I) by the length (column M), to get the Amount per week.

In columns P:AO I've added IF formulas to map the Amount to the right date. How ever, certain dates don't get add up.

Row is 300,000, 4 weeks, or 75,000 per week. But when I look at AB3 - it has no Amount associated, even though it's in between the right dates, and I can't figure out why it won't map.

Similar errors in the other rows..

Many Thanks,

eyeope

[Kaper]

You have Sundays in row 2 while in columns J and K you have different weekdays. Probably this shall do, but please chceck it:

Please Login or Register  to view this content.

(change commas to semicolons if your language version requires so),

[eyeope]

Thanks Kaper,

For example, I updated K6 to 20/1/2015 - The IF formulas give me an extra week instead of updating? (When i use the +6)..

[daddylonglegs]

I'm not clear where the payments should fall.

If you have start date on a Thursday, e.g. 8 Jan 2015 and then end date the following Wed, 14 Jan 2015 then your formulas give 1 payment falling in week ending 11 Jan 2015 - is that correct.....and if so then what's the logic of the payment being in that week and not w/e 18 Jan? Is the payment deemed to be on a specific day?

[eyeope]

Thanks for answering!

Basically, if I have 150,000 running though a periods of 3 weeks, I want it to map the 150,000 per each week. So, 50 per week - if that makes sense?

[daddylonglegs]

Basically, if I have 150,000 running though a periods of 3 weeks, I want it to map the 150,000 per each week. So, 50 per week - if that makes sense?

Yes, I understand that part, but which 3 weeks? In my example there is only 1 week, so only 1 payment, but the period stretches across 2 weeks so in which week should that fall?

[eyeope]

I get what you're saying, and I guess that's the reason i'm stuck.

For example,

If I have 150,000 through 1/1/2015 - 20/1/2015 - it's a 3 week period, right? But technically according to my dates it should be mapped out on 4 date periods, since they're within the periods below:

4/1/2015
11/1/2015
18/1/2015
25/1/2015

So, ideally I'd like it to map 37,500 on each week, but not sure how to, since in columns L, M & N it's mapped "correctly" as 3 weeks.

Any suggestions?

[daddylonglegs]

OK, two possible suggestions for you - split the amount equally between all the weeks that are intersected by the date range - so if you have a Sunday to Sunday date range two weeks apart that's 15 days, 7 in two weeks but a single day in the other week. You would split the 150,000 into 3 lots of 50,000

....or you could do it by day, so you split 70,000, 70,000, 10,000 in my example

It's relatively simple to produce formulas for either scenario - do you have a preference?

[eyeope]

Many Thanks for the quick response.

The 70,70,10 scenario would actually fit perfectly :-)

[daddylonglegs]

To split based on days in each week try this formula in P2 copied across and down

=MAX(0,MIN(P$2,$K3)-MAX(P$2-6,$J3)+1)*$I3/$L3

You don't really need columns M or N

[eyeope]

MANY THANKS!!!

Works like a charm.