Fourth friday in each month

[a massive zebra]

Is it possible to write a formula to find the date of the fourth friday in each month?

Thanks

 

[barry houdini]

If you have any date in A1 this formula in A2 will find the 4th Friday in that month

=A1-DAY(A1)+29-WEEKDAY(A1-DAY(A1)+2)

If you then want a list of subsequent 4th Fridays you can use this formula in A3 copied down

=A2+28+(DAY(A2+28)<22)*7

format all cells in required date format

 

[a massive zebra]

Thanks Barry that works great!

I don't quite understand how the formula works and would like to do this for other occasions such as the third tuesday. Could you show me how to do this so I can understand what needs to be changed to get a required day?

Thanks so much!

 

[silkfire]

=A1-DAY(A1)+29-WEEKDAY(A1-DAY(A1)+2)

I don't see a number 4 and 5 (position of Friday in the week) in your formula, how did you make it work?

 

[barry houdini]

Here's a generic version of the first formula

=A1-DAY(A1)+1+n*7-WEEKDAY(A1-DAY(A1)+8-xday)

where n would be the instance number and xday the day (1=Sun through to 7=Sat) so 3rd Tuesday would be

=A1-DAY(A1)+1+3*7-WEEKDAY(A1-DAY(A1)+8-3)

Then the second formula can be amended based on the instance number only, generic version is

=A2+35-(ABS(DAY(A2+35)-n*7+3)>3)*7

n should be 1 to 4 only (if you use 5 then the first formula will give 5th instance if the month if there is one, but 1st instance of the next month if there isn't - 2nd formula won't work)

 

[a massive zebra]

Thanks Barry, that's great.