# Formulas To Perform Day Of Month Calculations

Category: Formulas | [Item URL] 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. ### Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy