Excel Problem

[Spogs]

I've got an Excel problem that I really need some help with fairly urgently. I'm doing a weekly cash flow and want all my incomes and outgoings to fall into a column so everything due that week will fall into that column. So across the top I've got column headings of week ending C2 = "11/2/09", D2="18/3/09", E2= "25/3/09", F2 = "1/4/09" etc. for say 3 months (column headings are just add 7 to the previous DATEVALUE).

Down the side I've got an amount and a payment due date. So I might have a lease payment of $400 due on the 17th day of every month. I want that payment amount to fall into the correct weekly column for the next 3 months.

eg. Row 4 Column A = $400 Row 4 Column B = 17.

So a payment will appear in the week where the 17th falls. ie. week ending 18th will show a payment of $400. That's easy I can just use if DAY(B4) < DAY(D2) and > DAY (D1) then it falls into that column.

Where the problem is is at the end of the month where in this example a payment falls on the 28th of each month. The 28th is not between 25th and the 1st, so it won't fall in the slot.

I can use DATEVALUE but I've only got the day of the month as the payment date and DATEVALUE needs to get a month and year from somewhere. I can use column headings to extract i.e =MONTH(F2) but that will change a DATEVALUE from say 28/3/09 to 28/4/09 and throw the criteria out.

Hope you can understand this and give me some help. It's a bit complicated but there must be an easier solution. It has to be a common requirement for us accountants.

Thanks,

 

[izzyfoshizzy04]

I had the same issue a while back. I will get back to you.

 

[red+black]

i started to look at this and thought why bother if you can't either provide a screenshot or at least put some data in CODE tags that make it look like rows and columns.

help us to help u.

 

[Spogs]

Red+Black,

Yeah sorry about that but I'm not to good on that sort of thing. Never had a need to learn it. Still learning how to use the mouse thingy.

It all looked so simple when I started out on the problem.

 

[red+black]

   A    B       C       D       E      F
1
2            11/3/09 18/3/09 25/3/09 1/4/09
3
4 $400  17

I don't understand where on one hand you say payments falls on the 17th, then you go onto say that payments fall on the 28th.

You did not specify where the 28th is on your spreadsheet. Why are there two payment dates?

 

[red+black]

For payment_date = 28 and comparing to 25/3/09 and 1/4/09

do 1/4/09 - B4&"/"&MONTH(C2)&"/"&YEAR(C2)
and B4&"/"&MONTH(C2)&"/"&YEAR(C2) - 25/3/09

if they are both >=0, it's a payment week.

 

[Spogs]

The 17th was just an example because it works fine the way I was doing it, the 28th didin't. We've got about 130 automatic payments each month that fall on different days.

Your solutions makes good scense though. Thanks a lot for that I'll try it on Tuesday.

 

[Falchoon]

Laymans solution

Insert 4 rows above date

ie 18/3/09 in row 4 column e

in row 1 =IF(E3<7,E3,0)
in row 2 =DAY(D4)
in row 3 =DAY(E4)
in row 4 =IF(E2<E1,31,E2)

Formula

=IF($B7>E$2,IF($B7<=E$4,$A7,0),0)+IF($B7<=E$1,$A7,0)

 

[red+black]

and i thought i had too much time on my hands

 

[Falchoon]

and i thought i had too much time on my hands

I was trying to work something else out in excel at the same time

I could use sumproduct but that involved labelling all the rows which i din't want to do, I gave up, did sumproduct and did this instead.

 

[Spogs]

Sensational Falchoon,

Works perfectly, I haven't been able to fault it. Thanks for your and others help. I was getting carried away with CONCATENATE and DATEVALUE etc. and ended up making it way too complicated.

Regards,