exceldatetime

Excel function to determine the last Friday in a month

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
Solution

  • 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)