Formulas To Perform Day Of Month Calculations

A companion file is available: Click here to download

Many events are scheduled for a particular occurrence of the day within a month. For example, payday might be the last Friday of every month. Or, a meeting might be scheduled for every second Monday of the month.

Excel doesn't have a function that can calculate these types of dates, but it's possible to create a formula. In the figure below, the formula in cell D4 calculates the date based on the parameters in column C. The formula in D4 is:

=DATE(C3,C4,1+((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+(C5-WEEKDAY(DATE(C3,C4,1))))

This formula is not always accurate, however. If you specify a day number that doesn't exist (for example, the 6th Friday), it returns a date in the following month.

Cell D6 contains a modified formula that displays "(none)" if the date isn't in the month specified. This formula is much longer:

=IF(MONTH(DATE(C3,C4,1+((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+
(C5-WEEKDAY(DATE(C3,C4,1)))))<>C4,"(none)",DATE(C3,C4,1+
((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+(C5-WEEKDAY(DATE(C3,C4,1)))))

In some cases, you might need to determine the last occurrence of a day in a particular month. This calculation requires a different formula (refer to the figure below):

=DATE(C9,C10+1,1)-1+IF(C11>WEEKDAY(DATE(C9,C10+1,1)-1),
C11-WEEKDAY(DATE(C9,C10+1,1)-1)-7,C11-WEEKDAY(DATE(C9,C10+1,1)-1))

In this figure, the formula in cell D10 displays the date of the last Friday in March, 2008.

The download file for this tip contains another example that has an easy-to-use interface. The user can select the parameters from drop-down lists. The megaformula in the Calculated Date column is very complex because it needs to covert words into values.