To add:
I actually use this formula:
=(DATE(YEAR(B4),MONTH(B4)+1,1)-1)

Which worked last week (last week's date was 11/24/2008, so the result was
11/30/2008). This week however, it's given me a #VALUE. And I think it has
to do with the fact that today's date is 12/1/2008. I think that because
it's December, it knows to roll to January, but then it doesn't know to roll
the year for some reason. I read online that it should recognize a 13 for
January, and to increment the year, but it hasn't. It then just gives me a
#VALUE or a #NUM error.

Any advice would be greatly appreciated.

"Susan" wrote:

I too have this problem, and when trying the formula below, it gives me a
#NUM error. I'm using 2003 Excel.

Thanks,
Susan

"Rick Rothstein" wrote:

Try this formula...

=DATE(YEAR(A1),MONTH(A1)+1,0)

--
Rick (MVP - Excel)


"tigermoth" wrote in message
...
If Cell A1 is 29/07/2008, how to I formulate cell D1 to show 31/07/2008,
15/08/2008 to 31/08/2008 etc ( ie rounding up the date in column A to show
month end date in column D.

Thanks