Insert a value based on a date range from other cells

[octavio]

Budget_Test.xls
	A	B	C	D	E	F	G	H	I	J
1	Payment	PaymentByThis	01/12/07	01/26/07	02/09/07	02/23/07	03/09/07	03/23/07	04/06/07	04/20/07
2	Amount	DayEachMonth
3	100	22	100		100		100			100
4
5
6
7		EachcolumndaterepresentsaFriday14daysapart
8		IwanttomakeapaymentrepresentedinA3ifthedatefallswithinanytwoweekperiod
Budget

 

[NBVC]

I want to make a payment represented in A3 if the date falls within any two week period

What date are you referring to?

 

[octavio]

in this particular example it is the 22nd of each month which is is cell b3. I just want to automate the process by having the payment inserted in the right cell based on the date.

 

[Peter_SSs]

octavio

See if this is headed in the right direction. Note that my dates are in Australian format (dd/mm/yyyy)

Formula in C3 (copied across and down):

=IF(AND(DAY(C$1)<=$B3,OR(DAY(C$1+14)>$B3,MONTH(C$1)<>MONTH(C$1+14))),$A3,"")

Is it possible that you could have data where the payment day is below 14 (like row 6 in my example)? If, so what results would you expect for row 6?

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J
1	Payment	Payment By This	12/01/2007	26/01/2007	09/02/2007	23/02/2007	09/03/2007	23/03/2007	06/04/2007	20/04/2007
2	Amount	Day Each Month
3	100	22	100		100		100			100
4	150	15	150		150		150		150
5	200	27		200		200		200		200
6	250	3
7
Payments