Business Forecasting tool with Dynamic Dates.....HELP!!!!

[Mark McInerney]

Hi All,

I am trying to build a cash flow forecasting tool. I have revenue that comes into a business on the nth day of certain months - this data is captured on the right hand side of the table below.

in the data below as an example, on the (Dynamic Date) 1st of Jan thru Dec the revenue table states what comes in each month regardless of the year.

My formula is working fine but I am having trouble ONLY when the year changes. In the example below you can see nothing populates, when I expect €1,505 to be the result.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30/12/2018[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]DYNAMIC DATE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5/1/2019[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=IF(AND(DATEVALUE(CONCATENATE(N3,"/",B2,"/",YEAR(A1)))>=(A1-1),DATEVALUE(CONCATENATE(N3,"/",B2,"/",YEAR(A1)))<A2),B3,0)[/TD]
[TD]1505[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD]1000[/TD]
[TD]1200[/TD]
[TD]1500[/TD]
[TD]1260[/TD]
[TD]1300[/TD]
[TD]1400[/TD]
[TD]1500[/TD]
[TD]1600[/TD]
[TD]1700[/TD]
[TD]1

[/TD]
[/TR]
</tbody>[/TABLE]


I'm not certain if the table illustrates it properly - if anyone can advise as to how to insert a screen scrape I can forward.

The actual formula is :

=IF(AND(DATEVALUE(CONCATENATE($GE$12,"/",$FP$7,"/",YEAR(DC6)))>=(DC6-1),DATEVALUE(CONCATENATE($GE$12,"/",$FP$7,"/",YEAR(DC6)))<DC7),$FP$12,0)

Any help greatly appreciated.

Best - Mark.

 

[Rijnsent]

Hi Mark,
I'm trying to understand your issue, but in your example you only have info for 1 year, right?
-So basically your money would come in on january 1st, february 1st etc (or 2nd, 3rd, whatever you put in dynamic date)?
-And than you'd like to add up all the amounts that are between the dates in A1 and A2?
-Is there data for various years or do you just have 12 numbers representing multiple years?
Hope you can help us a bit with some details.
Thanks,
Koen

 

[Mark McInerney]

Hi Koen,

Thank you for taking the time to review my post - appreciated.

Is it possible for me to insert a screen scrape on this message board?

 

[Rijnsent]

Hi Mark,
nope, check out my signature for an excel-to-html tool or use dropbox/google drive.
Koen