To show month end date from any date entered

[tigermoth]

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

 

[Rick Rothstein]

Try this formula...

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

 

[Susan]

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

Thanks,
Susan

 

[Susan]

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.

 

[Bernard Liengme]

This suggest that A1 is not a real date. What does =A1+1 return? It should
be the next day
best wishes

 

[Susan]

Assume that A1 is the date I manually put in:
If I put 11/23/2008, A1+1 would then be 11/24/2008.
DATE(YEAR(A1),MONTH(A1)+1,1)-1 gave me 12/1/2008-1 = 11/30/2008
Today, I put in 12/1/2008, A1+1 gives me 12/2/2008.
DATE(YEAR(A1),MONTH(A1)+1,1)-1 SHOULD give me 13/1/2008-1 = 12/31/2008
The result I want is 12/31/2008. But for some reason it's not handling the
December date very well, and is giving me a #VALUE error.

When I try to use DATE(YEAR(A1),MONTH(A1)+1,0, it gives me a #NUM error
(even when I used a November date).

 

[MyVeryOwnSelf]

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.

Check out the EOMONTH function in Excel 2003 built-in Help.

 

[Bernard Liengme]

Please send me (my private email, not the newsgroup) a sample file
best wishes