Excel : the first and last day of month with and without weekends or holidays

This post shows how to determine the first or last day of a month taking weekend or holidays into account using Excel functions with a user-defined table of holidays. This can be applied when generating a schedule of IRS swap cash flows.

Excel : the first and last day of month with and without weekend or holidays

There are some cases where the first or last day of a month needs to be determined taking weekend or holidays into account. A typical example is to make a schedule of interest rate or currency swap cash flows.

Using Excel functions and user-defined table of holidays, we can determine these dates simply.

Excel Formula

For example, the following Excel formula are used for the 3rd row in the speadsheet.

A) C3=DATE(LEFT(B3,4), MID(B3,6,2),1) B) D3=C3+IF(WEEKDAY(C3)=1,1,IF(WEEKDAY(C3)=7,2,0)) C) E3=WORKDAY(C3-1,1,$B$14:$H$22) D) F3=EOMONTH(C3, 0) E) G3=F3-IF(WEEKDAY(F3)=1,2,IF(WEEKDAY(F3)=7,1,0)) F) H3=WORKDAY(F3+1,-1,$B$14:$H$22)

A) means the first day of a month. In B), the date moves forward to the first weekday taking Saturday and Sunday into account and in C) that date moves forward more with consideration of holidays. Conversely, D), E), and F) are the cases of the end of a month.

In particular, $B$14:$H$22 is a range of user-defined holidays which tends to differ country by country.

Period.