Catch first Friday in a month or Next Friday

[mrxlsx]

Hello all,

I have a date column A2 with random dates. In the next column B2, I want to catch the first Friday of that month, if it A2 is later than the current month's first Friday, then I have catch first Friday of the next month. This is one problem.

Another problem is: In another sheet, I have same date column A2 with random dates. In the next column B2, I want to catch the first Friday of the current month, if A2 is later than the current month's first Friday then second Friday, if it misses then third Friday, if it misses then fourth Friday.

What function should I use for these to work ??

thanks for everybody who try this !!

 

[skorpionkz]

first formula:

=IF(DAY(A2+(7-WEEKDAY(A2,16)))<=7,A2+(7-WEEKDAY(A2,16)),(1&"/"&MONTH(A2)+1&"/"&YEAR(A2))+(7-WEEKDAY((1&"/"&MONTH(A2)+1&"/"&YEAR(A2)),16)))

second formula:

=A2+(7-WEEKDAY(A2,16))

 

[skorpionkz]

small update to the first formula, so it should work in December aswell:

=IF(DAY(A2+(7-WEEKDAY(A2,16)))<=7,A2+(7-WEEKDAY(A2,16)),(1&"/"&IF(MONTH(A2)=12,1,MONTH(A2)+1)&"/"&IF(MONTH(A2)=12,YEAR(A2)+1,YEAR(A2)))+(7-WEEKDAY((1&"/"&IF(MONTH(A2)=12,1,MONTH(A2)+1)&"/"&IF(MONTH(A2)=12,YEAR(A2)+1,YEAR(A2))),16)))

 

[mrxlsx]

Hi skorpionkz, This formula is working just fine. Thank you so much.

mrxlsx

 

[ITWare2008]

Hi mrxlsx,

For first friday

=DATE(YEAR(J80);MONTH(J80);1+(6-WEEKDAY(DATE(YEAR(J80);MONTH(J80);1))))

for nth friday

=IF(WEEKDAY(J80;16)<7;J80+7-WEEKDAY(J80;16);J80)

 

[skorpionkz]

first formula doesn't work for the condition when the first Friday already pass.

 

[ITWare2008]

Hi Skorpionkz,

Unless I am missing your point if the first Friday of the month already pass, you should use formula for nth Friday.

Vândalo

 

[skorpionkz]

if it A2 is later than the current month's first Friday, then I have catch first Friday of the next month. This is one problem.

There was 2 separate conditions in the thread.</SPAN>

 

[ITWare2008]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Today
(B1 ->)[/TD]
[TD]=today()[/TD]
[/TR]
[TR]
[TD]weekday of 1st of month
(B2 ->)[/TD]
[TD]=weekday(date(year(today());month(today());1);16)[/TD]
[/TR]
[TR]
[TD]1st Friday
(B3 ->)[/TD]
[TD]=date(year(today());month(today());1+(7-B2))[/TD]
[/TR]
[TR]
[TD]today>1st Friday
(B4 ->)[/TD]
[TD]=b1>b3[/TD]
[/TR]
[TR]
[TD]weekday of 1st of next month (B5 ->)[/TD]
[TD]=weekday(date(year(today());month(today())+1;1);16)[/TD]
[/TR]
[TR]
[TD]1st Friday of next month
(B6 ->)[/TD]
[TD]=date(year(today());month(today())+1;1+(7-B5))[/TD]
[/TR]
</tbody>[/TABLE]
Ok,

this is my solution if your A2 already pass current month's first Friday:

=IF(TODAY()>DATE(YEAR(TODAY());MONTH(TODAY());1+(7-WEEKDAY(DATE(YEAR(TODAY());MONTH(TODAY());1);16)));DATE(YEAR(TODAY());MONTH(TODAY())+1;1+(7-WEEKDAY(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);16)));DATE(YEAR(TODAY());MONTH(TODAY());1+(7-WEEKDAY(DATE(YEAR(TODAY());MONTH(TODAY());1);16))))

The formula above could be simplified if you use the above table and write the following formula

=if(b4;b6;b3)

Hope this help

Vândalo