Not blank under a condition

[phil133]

Hello. I would like a formula (or macro) that will give me the values of a column under a condition: The date that corresponds to this value is 5 business days before or up to the same day of the third Friday of the month.

An example to understand.
Column A has business days starting from 31/01/2006. Column B has different numbers in each cell. Column C has the dates of third Fridays of the month starting from 17/02/2006. In column D I would like column B but only if the date is 5 business days or closer from the third Friday of that month. (I have the formula for the third Friday of the month: =DATE(2006, ROWS(A$2:A2), 1 + 7*3) - WEEKDAY(DATE(2006, ROWS(A$2:A2), 1 - 6))

row/column	A	B	C	D
1	31/1/2006	4	17/2/2006
2	1/2/2006	3	17/3/2006
3	2/2/2006	3	21/4/2006
4	3/2/2006	68	etc
5	6/2/2006	9
6	7/2/2006	9
7	8/2/2006	1
8	9/2/2006	2
9	10/2/2006	3
10	13/2/2006	4		4
11	14/2/2006	5		5
12	15/2/2006	1		1
13	16/2/2006	2		2
14	17/2/2006	3		3
15	20/2/2006	4
16	21/2/2006	5
17	etc	6
18	8/3/2006	5
19	9/3/2006	7
20	10/3/2006	3
21	13/3/2006	1		1
22	14/3/2006	9		9
23	15/3/2006	7		7
24	16/3/2006	6		6
25	17/3/2006	8		8
26	20/3/2006	4
27	21/3/2006	5
28	22/3/2006	9
29	23/3/2006	2

<tbody>
</tbody>

Thanks for any help!

 

[RCBricker]

Hello. I would like a formula (or macro) that will give me the values of a column under a condition: The date that corresponds to this value is 5 business days before or up to the same day of the third Friday of the month.

An example to understand.
Column A has business days starting from 31/01/2006. Column B has different numbers in each cell. Column C has the dates of third Fridays of the month starting from 17/02/2006. In column D I would like column B but only if the date is 5 business days or closer from the third Friday of that month. (I have the formula for the third Friday of the month: =DATE(2006, ROWS(A$2:A2), 1 + 7*3) - WEEKDAY(DATE(2006, ROWS(A$2:A2), 1 - 6))

row/column	A	B	C	D
1	31/1/2006	4	17/2/2006
2	1/2/2006	3	17/3/2006
3	2/2/2006	3	21/4/2006
4	3/2/2006	68	etc
5	6/2/2006	9
6	7/2/2006	9
7	8/2/2006	1
8	9/2/2006	2
9	10/2/2006	3
10	13/2/2006	4		4
11	14/2/2006	5		5
12	15/2/2006	1		1
13	16/2/2006	2		2
14	17/2/2006	3		3
15	20/2/2006	4
16	21/2/2006	5
17	etc	6
18	8/3/2006	5
19	9/3/2006	7
20	10/3/2006	3
21	13/3/2006	1		1
22	14/3/2006	9		9
23	15/3/2006	7		7
24	16/3/2006	6		6
25	17/3/2006	8		8
26	20/3/2006	4
27	21/3/2006	5
28	22/3/2006	9
29	23/3/2006	2

<tbody>
</tbody>

Thanks for any help!

This formula eliminates the C column. It will calculate if the date in B is within (+/-) 5 days of the third Friday of the month for the date in B.

=IF(AND(SUM(B2-DATE(YEAR(B2),MONTH(B2),1+7*3)-WEEKDAY(DATE(YEAR(B2),MONTH(B2),8-6)))<=5,SUM(DATE(YEAR(B2),MONTH(B2),1+7*3)-WEEKDAY(DATE(YEAR(B2),MONTH(B2),8-6))-B2)>=-5),C2,"")

 

[phil133]

Thanks for your effort but this only excludes (from appearing) the values after the third Friday. For example, I get all the values from 1/2/2006 to 22/02/2006.

 

[RCBricker]

Thanks for your effort but this only excludes (from appearing) the values after the third Friday. For example, I get all the values from 1/2/2006 to 22/02/2006.

my bad. had a meeting couldn't test. The below formula has been tested and works.

=IF(AND(SUM(B2-(DATE(YEAR(B2),MONTH(B2),1+7*3)-WEEKDAY(DATE(YEAR(B2),MONTH(B2),8-6))))<=5,SUM(B2-(DATE(YEAR(B2),MONTH(B2),1+7*3)-WEEKDAY(DATE(YEAR(B2),MONTH(B2),8-6))))>=-5),C2,"")