» Calculating the Date of the Nth Specific Day of the Month
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
Calculating the date of, for example, the first Saturday of each of the months listed in column A, for the year specified in cell B2.
Solution:
Use the WEEKDAY and DATE functions as shown in the following formula:
=DATE($B$2,A2,1+((1-(6>=WEEKDAY(DATE($B$2,A2,1),2)))*7+(6-WEEKDAY(DATE($B$2,A2,1),2))))
Book Store:
Recommended Books:
How do I take account of the nth day. ie which part of the formula changes with say the third Friday? Also any ideas o how to drag out the last day of a month?