Cash Flow Manager application....Need smart date formulas...Please Help...

[Mark McInerney]

Hi All - Thanks for taking time to view this.

I am writing a cash flow manager application based in Excel 2010. My client enters a date in to Cell A1 and I want to be able to forecast cash flows from that date based on historic banking data that I have normalised.

The transactions on the account can be broken down into the following categories:

Every Monday, Tuesday, Wednesday, Thursday, Friday - I have these covered with =A1+(7-WEEKDAY(C1,2)+1) and changing the number added to allow for the different days of the week.

There are others that are linked to the nth day of every month - for these I use =EOMONTH(C1,0)+5. My problem is that the bank do not process payments in a Saturday or Sunday, so I need the formula to allow for this. If the formula =EOMONTH(C1,0)+5 is a Saturday or Sunday I need it to go to the next Monday which will be the 6th or 7th.

I also have to allow for VAT Calculations for my clients. These fall on the 23rd of every second month following pattern , Jan, Mar, May, Jul, Sep, Nov. What date is entered into A1, I need a formula that brings it to the next date.

There are some payment that are quarterly - much the same logic as above.

Really appreciate some help on this one - I'm stuck!

Many Thanks,

Mark.

 

[Andrew Poulsom]

You can use the WORKDAY function to ignore weekends, eg:

=WORKDAY(EOMONTH(C1,0),5)

Edit:

For the VAT payment date try:

=DATE(YEAR(A1),MONTH(A1)+(MOD(MONTH(A1),2)=0),23)

 

[Mark McInerney]

Hi Andrew,

Thanks for reply - appreicated.

Cell C1=14/09/2012

=WORKDAY(EOMONTH(C1,0),10)

I needed to reference the 10th day of the following month so I changed the 5 to a 10? I assume this is OK? However the returned value is [TABLE="width: 163"]
<tbody>[TR]
[TD="class: xl66, width: 83, align: right"]12/10/2012[/TD]
[TD="class: xl65, width: 80, align: right"]Friday

Am I doing something wrong?

Thanks, Mark.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mark McInerney]

The VAT one works fine Andrew - Thanks.

Just a slight issue I am having. As I change the date it updates fine - as per the cycle I outlined which is brilliant. I want to predict cash flows for 4 months out so what I have done is I reference the initial call output in the following cell, but nothing changes?

Do I need to adjust something?

Cell I520 is =DATE(YEAR(C1),MONTH(C1)+(MOD(MONTH(C1),2)=0),23)
Cell I521 is =DATE(YEAR(I520),MONTH(I520)+(MOD(MONTH(I520),2)=0),23)

In this case Cell I520 and I521 are the same?

Thanks again for help.

 

[Andrew Poulsom]

Try:

=WORKDAY(EOMONTH(C1,0)+10,--(WEEKDAY(EOMONTH(C1,0)+10,2)>5))

 

[Mark McInerney]

Hi Andrew,

One word - Genius!

 

[Andrew Poulsom]

For I521 I think you just need:

=DATE(YEAR(I520),MONTH(I520)+2,23)