Not blank under a condition

phil133

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
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/columnABCD
131/1/2006417/2/2006
21/2/2006317/3/2006
32/2/2006321/4/2006
43/2/200668etc
56/2/20069
67/2/20069
78/2/20061
89/2/20062
910/2/20063
1013/2/200644
1114/2/200655
1215/2/200611
1316/2/200622
1417/2/200633
1520/2/20064
1621/2/20065
17etc6
188/3/20065
199/3/20067
2010/3/20063
2113/3/200611
2214/3/200699
2315/3/200677
2416/3/200666
2517/3/200688
2620/3/20064
2721/3/20065
2822/3/20069
2923/3/20062

<tbody>
</tbody>

Thanks for any help!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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/columnABCD
131/1/2006417/2/2006
21/2/2006317/3/2006
32/2/2006321/4/2006
43/2/200668etc
56/2/20069
67/2/20069
78/2/20061
89/2/20062
910/2/20063
1013/2/200644
1114/2/200655
1215/2/200611
1316/2/200622
1417/2/200633
1520/2/20064
1621/2/20065
17etc6
188/3/20065
199/3/20067
2010/3/20063
2113/3/200611
2214/3/200699
2315/3/200677
2416/3/200666
2517/3/200688
2620/3/20064
2721/3/20065
2822/3/20069
2923/3/20062

<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.

Code:
=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,"")
 
Upvote 0

phil133

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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.

Code:
=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,"")
 
Upvote 0

Forum statistics

Threads
1,191,004
Messages
5,984,116
Members
439,872
Latest member
noaman79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top