Return Last Calendar Day of Month

I am trying to write something that reads the value, either M or Q, from a cell, and finds either the latest quarter end or month end date. For example:

If the current date is today, 12/12/2007, M would return 11/30/2007, and Q would return 9/30/2007.

I guess my question is, how do I return the last calendar day of a month?

=IF(B1 = "M", EOMONTH(A1, 0), IF(B1 = "Q", EOMONTH( DATE( YEAR(A1), CEILING(MONTH(A1), 3), 1), 0) ) )

Requires the Analysis ToolPak add-in.

Thanks for the suggestions. I ended up using the method in the first response and produced the code below:

Please Login or Register  to view this content.