Compare dates and return result for Cash flow forecast

whats the issue
=IF(D$1=($C3+(VLOOKUP($A3,$A$22:$C$25,2))+(VLOOKUP($A3,$A$22:$C$25,3))),$B3,0)

In first example

D1 = 1st Jan 21
Then you add the vlookup for A3 which is 1 , 2nd column so 30
then you add the vlookup for A3 which is 1 , 3rd column so 60
which = 1st April 2021

BUT D1 = 1/1/21 and so does not = 1/4/21 and so 0 returned


what result did you want ?

Your issue has not been adequately described. In addition, it would be very helpful if you provided a mocked up solution so we can better understand what you are trying to do.

that makes sense now
BUT in C3 you have 1st of month and rest of column has endof month - same for the Row 1 D1 is the 1st C1 , d1 etc is the end of month
So should that be the end of month 31st Jan ?

using the the formula
if c3 set to 31/jan/21 - then you get a date of 1st may 21
if c3 set t0 1/jan/21 - then you get a date of 1 april

So can you give some more examples of where you want the amount to land

i want to move the USD 10.00 to April(G3)

That makes sense for dates 1st - BUT the other dates are the end of month

We can test the date drived against the 2 headers to see if > then or < to determine which column
So using AND
AND ( header > formula , header2 < formula ) to see which column the date falls into

Thank you, please check my updated attachment, so based on what you suggested, i tried the formula =IF(D$1>=($C3+(VLOOKUP($A3,$A$23:$C$25,2))+(VLOOKUP($A3,$A$23:$C$25,3))),IF(D$1<=($C3+(VLOOKUP($A3,$A$23:$C$25,2))+(VLOOKUP($A3,$A$23:$C$25,3))),$B3,0),0)

it works, however not as i wanted to because the amount landed exactly on D3 so its (C3)January=(D1)January=(D3)USD 10) even though my lookup table moved it to April (G3).

what i want is, say (C3)January=(D1)January=i want the USD 10 to land in April(G3) given my lookup table values; with my above formula, i checked G3 and it is "TRUE" however the amount was still "0"

as you said earlier that;
D1 = 1st Jan 21
Then you add the vlookup for A3 which is 1 , 2nd column so 30
then you add the vlookup for A3 which is 1 , 3rd column so 60
which = 1st April 2021

BUT D1 = 1/1/21 and so does not = 1/4/21 and so 0 returned

Originally Posted by etaf

that makes sense now
BUT in C3 you have 1st of month and rest of column has endof month - same for the Row 1 D1 is the 1st C1 , d1 etc is the end of month
So should that be the end of month 31st Jan ?

using the the formula
if c3 set to 31/jan/21 - then you get a date of 1st may 21
if c3 set t0 1/jan/21 - then you get a date of 1 april

So can you give some more examples of where you want the amount to land

- i want the amount to land 90days after the date in C3

That makes sense for dates 1st - BUT the other dates are the end of month

We can test the date drived against the 2 headers to see if > then or < to determine which column
So using AND
AND ( header > formula , header2 < formula ) to see which column the date falls into

I am interested more on the MONTHS, as long as they are on the same months regardless of day

I have changed the 2 dates in headers and rows
As i still not sure i understand why
D1 = 1st , but C1, D1, E1 etc has the end of the month and not the 1st of month , need to be consistent - so i changed d1 to 31/1/21

THEN the column
C3 again says the 1st of month , BUT c4,c5,c6 etc all have the last day of month

Anyway
I have used this formula

=IF(AND(($C3+(VLOOKUP($A3,$A$22:$C$25,2))+(VLOOKUP($A3,$A$22:$C$25,3))-1)<=D$1,($C3+(VLOOKUP($A3,$A$22:$C$25,2))+(VLOOKUP($A3,$A$22:$C$25,3))-1) >= DATE(YEAR(D$1),MONTH(D$1),DAY(1))),$B3,0)

See if that works
Spreadsheet attached

At the moment its checking to see if C3+ "x" days falls between the 1st or end of a month

So at the moment you are adding 30 or 90 days

Which will be towards the end of a month around 28th+
So we have to test to see if that date 28th, 29th, 30th, 31st etc is assigned to that column for the header date, which only has end of month hence the AND ( is the date > or = 1st and less than or = to the end of the month )

maybe possible to shorten , but thats the logic i used

AND(($C3+(VLOOKUP($A3,$A$22:$C$25,2))+(VLOOKUP($A3,$A$22:$C$25,3))-1)<=D$1. Is the date less than or equal to the end of the month -as D1 shows the last day of month

AND
($C3+(VLOOKUP($A3,$A$22:$C$25,2))+(VLOOKUP($A3,$A$22:$C$25,3))-1) >= DATE(YEAR(D$1),MONTH(D$1),DAY(1)))
is the date greater or equal to the 1st of the month

So if any date produced by adding days to C , and is between those 2 limits , then its TRUE as BOTH formula have to be TRUE - hence the use of an AND()

So if you happen to change your days , say a type 4 code of 15 days , then it will still work

you are welcome