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

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

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

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

This suggest that A1 is not a real date. What does =A1+1 return? It should
be the next day
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Susan" wrote in message
...
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

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).

"Bernard Liengme" wrote:

This suggest that A1 is not a real date. What does =A1+1 return? It should
be the next day
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Susan" wrote in message
...
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

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.

Please send me (my private email, not the newsgroup) a sample file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Susan" wrote in message
...
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).

"Bernard Liengme" wrote:

This suggest that A1 is not a real date. What does =A1+1 return? It
should
be the next day
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Susan" wrote in message
...
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