formula for last day of month with a twist

M

MelB

I need a formula that calculates the last day of the month, but if the date
falls on the last day of the month I need it to return the last day of the
next month.

If A1=5/1/07, i need 5/31/07
If A1=5/31/07, i need 6/30/07

Many thanks.
 
N

Niek Otten

=EOMONTH(A1,IF(EOMONTH(A1,0)=A1,1,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I need a formula that calculates the last day of the month, but if the date
| falls on the last day of the month I need it to return the last day of the
| next month.
|
| If A1=5/1/07, i need 5/31/07
| If A1=5/31/07, i need 6/30/07
|
| Many thanks.
|
|
 
P

pinmaster

Hi,

Try this:

=IF(A1=EOMONTH(A1,0),EOMONTH(A1,1),EOMONTH(A1,0))

Regards!
Jean-Guy
 
R

Ron Rosenfeld

I need a formula that calculates the last day of the month, but if the date
falls on the last day of the month I need it to return the last day of the
next month.

If A1=5/1/07, i need 5/31/07
If A1=5/31/07, i need 6/30/07

Many thanks.
Click to expand...

And, without the Analysis Tool Pak installed:

=A1+33-DAY(A1+1)-DAY(A1+33-DAY(A1+1))
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Last working day of a month 12
Adding months to date at end of month 4
summing the relevant columns 2
Last day of last month 5
Dates problem 1
Days in a month 7
Difference between dates returning full months and remaining days 5
Beginning of the month formula 8

Top