Excel function to determine the last Friday in a month

Question

I'm looking for an Excel function to return the last Friday in a month for a given date.

ie: Any date in the month as input will give the date of the last Friday as output.

14-July-09 should give 31-July-09
7-March-05 should give 35-March-09

Answer 1

The following formula achives this for a date in cell A1:

=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)

Answer 2

You can try =DATE(YEAR(A1),MONTH(A1)+1,1)-1+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1),-2,-3,-4,-5,-6,0,-1) to find the last friday of the month. Assumes A1 has the date.