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

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

--
Regards

Roger Govier


"TonyD" wrote in message
...
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, thank you for tip. Once I figured out how the formula worked it
achieved the desired result.

Thanks,

Tony

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"TonyD" wrote in message
...
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, 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" wrote:

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

--
Regards

Roger Govier


"TonyD" wrote in message
...
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

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.

--
Regards

Roger Govier


"TonyD" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"TonyD" wrote in message
...
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 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" wrote:

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.

--
Regards

Roger Govier


"TonyD" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"TonyD" wrote in message
...
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

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.

--
Regards

Roger Govier


"TonyD" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"TonyD" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"TonyD" wrote in message
...
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