Month formulas

A

Alan Smith

How can I set up a formula in excel that automatically adds one month to a
date in another cell???? Example a 26/04/04 to 26/05/04?

I want to automate this function so that by entering the first date the
worksheet will automatically calculate the next month.

This will be used for calculation such as a loan repayment that is on the
same date of each month. I.e.23rd of each month, 1st of month etc etc etc???

I cannot just add 28-31 as it will knock the profile out.

PLEASE HELP - IT IS DRINING ME MAD!
 
F

Fred Exley

Alan Smith said:
How can I set up a formula in excel that automatically adds one month to a
date in another cell???? Example a 26/04/04 to 26/05/04?

I want to automate this function so that by entering the first date the
worksheet will automatically calculate the next month.

This will be used for calculation such as a loan repayment that is on the
same date of each month. I.e.23rd of each month, 1st of month etc etc
etc???

I cannot just add 28-31 as it will knock the profile out.

PLEASE HELP - IT IS DRINING ME MAD!
Click to expand...

how about: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

if the date is in A1, and you want to add 1 month to it

-Fred
 
R

Ron Rosenfeld

On Tue, 26 Oct 2004 10:43:06 -0700, "Alan Smith" <Alan
How can I set up a formula in excel that automatically adds one month to a
date in another cell???? Example a 26/04/04 to 26/05/04?

I want to automate this function so that by entering the first date the
worksheet will automatically calculate the next month.

This will be used for calculation such as a loan repayment that is on the
same date of each month. I.e.23rd of each month, 1st of month etc etc etc???

I cannot just add 28-31 as it will knock the profile out.

PLEASE HELP - IT IS DRINING ME MAD!
Click to expand...

With your First Date in A1:

If you have the Analysis tool pak installed, then:

=EDATE(A1,1)

If you do NOT have the ATP installed, then:

=MIN(DATE(YEAR($H$25),MONTH($H$25)+G26,DAY($H$25)),
DATE(YEAR($H$25),MONTH($H$25)+G26+1,0))


--ron
 
M

Myrna Larson

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


On Tue, 26 Oct 2004 10:43:06 -0700, "Alan Smith" <Alan
 
F

Fred Exley

Ron Rosenfeld said:
What if the date in A1 is 31 Jan 2004?


--ron
Click to expand...

That's what I get for relying on the first thing Microsoft help shows.
=EDATE(A1,1) is 'the' way to go -thanks for the enlightenment!
-Fred
 
M

Myrna Larson

Before anybody can answer that, you need to tell us what *you* think the
answer should be <g>.
 
M

Myrna Larson

For that matter, you have a problem whenever the first date is the 31st of a
month.
 
R

Ron Rosenfeld

Before anybody can answer that, you need to tell us what *you* think the
answer should be <g>.
Click to expand...

Yes, you are absolutely correct, and it is a matter of choice.

However, I think the EDATE function (or it's non-ATP equivalent), is what I
would recommend.

In other words, don't run over the last date of the month.

And, the function should always be based on the Start Date + n months (rather
than always adding 1 month to the preceding formula result).


--ron
 
Top