Eomonth Formula Help

[DRExcel515]

Ok I have a column of dates and in cell A9 my formula reads as follows EOMONTH(A8,1). A8 has the date 11/30/2016 and the formula is bringing back 12/31/2016, but the last day of December in 2016 was 12/29/2016. Is there a way to tweak my formula to bring back the correct day end of the month prior? Any help would be much appreciated!

Cheers

 

[DRExcel515]

Actually I just realized the formula is pulling the correct end of month, what I meant was there a way to have it pull in the last business day of the month? Thanks!

 

[barry houdini]

You can do that by getting the first day of the following month and then going back one workday

=WORKDAY(EOMONTH(A8,1)+1,-1)

If you want to exclude holidays you can include a range of holiday dates at the end, e.g. assuming holidays in H2:H8

=WORKDAY(EOMONTH(A8,1)+1,-1,H$2:H$8)