dates in formulas

[Russ]

I would like my spreadsheet to add a month to a cell based upon the
value of the date in another cell. In other words, I would enter the
date in A1 and be formatted as Oct-2009, and I would like to put a
formula in A2 that would result in the display of Nov-2009. I've
tried =A1+1 but that just won't get it as it still returns Oct -09.

 

[JoeU2004]

I've tried =A1+1 but that just won't get it as it
still returns Oct -09.

Because you added one __day__, not one month. Try one of the following:

=date(year(A1),1+month(A1),day(A1))

=edate(A1,1)

If the latter formula causes a #NAME error, see the Help page for EDATE.

I suspect you will see no difference, depending on how you entered the
actual date.

But in general, the advantage of EDATE, besides being simpler to write, is
that if the date in A1 is greater than the 28th in Jan or the 30th of some
months, EDATE will ensure that the day of the calculated month is no greater
than the end of the month, which is usually what we want.


----- original message -----

 

[Russ]

Thanks. I used the edate after having to install it from disc.

 

[Shane Devenshire]

Hi,

I prefer EDATE, but here is another solution

=A1-DAY(A1)+32

Since you are formatting you dates as MMM-YY this formula should work just
fine.

 

[DILipandey]

Hi Russ,

you can use either of the following two formulas in cell A2.
=EOMONTH(A1,1)
or,
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+30)

Note:- Do the formatting as you are doing. MMM-YYYY
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India