dates in formulas

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.

0
Rusty276 (2)
6/14/2009 9:30:19 PM
excel 39879 articles. 2 followers. Follow

4 Replies
679 Views

Similar Articles

[PageSpeed] 18

"Russ" <Rusty@alwaysathome.net> wrote:
> 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" <Rusty@alwaysathome.net> wrote in message 
news:ajqa35pi9gsrclds7nkmrr8cjl1elra0ur@4ax.com...
>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.
> 

0
joeu2004 (766)
6/14/2009 9:58:49 PM
Thanks.  I used the edate after having to install it from disc.

On Sun, 14 Jun 2009 14:58:49 -0700, "JoeU2004" <joeu2004@hotmail.com>
wrote:

>"Russ" <Rusty@alwaysathome.net> wrote:
>> 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" <Rusty@alwaysathome.net> wrote in message 
>news:ajqa35pi9gsrclds7nkmrr8cjl1elra0ur@4ax.com...
>>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.
>> 
0
Rusty276 (2)
6/15/2009 1:11:56 AM
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.
-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Russ" wrote:

> 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.
> 
> 
0
6/15/2009 2:57:01 AM
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.)
dilipandey@yahoo.com
dilipandey@gmail.com
New Delhi, India


"Russ" wrote:

> 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.
> 
> 
0
6/15/2009 10:38:01 AM
Reply:

Similar Artilces: