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))
<tbody>
</tbody>
Thanks for any help!
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!