Payment cell populated based on date formula

[Guest]

I want to create a formula that checks a header date to see if it falls in
the range of six days before or six days after a monthly payment required
date. I tried using wildcards such ?? or ~ to substitute month or year in the
following example. 01 & 10 represent the date of month the bill is required
to be paid.



01/25/07 02/01/07 02/08/07 02/15/07 02/22/07
01 Maintenance 300.00
10 VISA $100.00

 

[Roger Govier]

Hi Tony

Assuming your dates start in D1 and go across the page, A2 contains the
day of month, B2 contains Description and C2 contains Amount, then enter
in D2
=IF(AND($A2>=DAY(D$1),$A2<=DAY(E$1)),$C2,"")
and copy across and down as required

 

[Guest]

Roger, thank you for tip. Once I figured out how the formula worked it
achieved the desired result.

Thanks,

Tony

 

[Guest]

Roger, i thought it was working but I have found that when I have a day of
the month (cell A2) that is equal to "1" it only appears when the dates
(cells D1, D2, etc.) are equal to "1". Also any high days of the month such
as "28" or "29" only place amounts in the cell when the second date is less
than "31". So if for example the date is "1" up to "27" of the month a days
value of "28", "29", "30" or "31" will not appear.

Any suggestions?

 

[Roger Govier]

Hi Tony

Yes, that was rather "sloppy thinking" on my part wasn't it!!!

Try
=IF(AND(MAX(DATE(YEAR(D$1),MONTH(D$1),$A2),
DATE(YEAR(E$1),MONTH(E$1),$A2))>D$1,
DATE(YEAR(D$1),MONTH(D$1),$A2)<=E$1),$C2,"")

This I think will always put the values in the week commencing with the
date in row 1.
It is all one long formula that I have split onto 3 lines to prevent the
newsreader breaking in awkward places.

 

[Guest]

Roger although it made progress, there still some errors:

1/25/07 2/01/07 2/08/07 2/15/07 2/22/07 3/01/07

1 300 300
10 200 200 200
15 29 29 29
15 250 250 250
15 93 93 93
21 103 103 103
28 20 20
29 330.25 330.25
29 110 110
29 170 170


Tony

 

[Roger Govier]

Hi Tony

I can see that the 1st gives a problem.
Also the 29th, as there is no 29th in February, but it works for all
other months.
The reason all the values show in the first column, is because there is
no date of the header of the previous column.

I can't stop to figure it out right now as I have to go to a client's
offices and will be out most of the day.
I will try to take a look later, unless someone else steps in with a
solution for you.