Month Formula
 

Is there a formula that would read the previous cell and insert the following
month? Ex. Prev Cell is "June" the next cell would read "July"?

Hi Todd,

That really depends if what you have in the cell is a date or text. When
you select a cell in question, what does it say in the Formula Bar? Is it
"June"? Or is a number/date *formatted* to show June (mmmm)?

If it's a date, check out the EOMONTH function from the Analysis Toolpak.

HTH

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
Is there a formula that would read the previous cell and insert the
following
month? Ex. Prev Cell is "June" the next cell would read "July"?

It is in text format. This is for my reviews and it pulls from somewhere
else on the sheet. Would a number/date format be easier to use?

"Zack Barresse" wrote:

Hi Todd,

That really depends if what you have in the cell is a date or text. When
you select a cell in question, what does it say in the Formula Bar? Is it
"June"? Or is a number/date *formatted* to show June (mmmm)?

If it's a date, check out the EOMONTH function from the Analysis Toolpak.

HTH

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
Is there a formula that would read the previous cell and insert the
following
month? Ex. Prev Cell is "June" the next cell would read "July"?

Hi Todd
One way
=DATE(YEAR(A1),MONTH(A1)+1,1)
where 01/06/2005 is in A1

--
Regards

Roger Govier


"Todd Nelson" wrote in message
...
Is there a formula that would read the previous cell and insert the
following
month? Ex. Prev Cell is "June" the next cell would read "July"?

Extremely. If you put a date in a cell, e.g. 1/1/2005, whatever, you can
then set a custom format (select cell | Format | Cells | Number (tab) |
Custom) of ..

mmmm

And it will only show "June", although it is actually a Serial Number in the
cell. Dates prove to be (IMHO) the best examples for using custom formats.

If you have it as text (I'll assume A2 for this example) you can use a
formula to *force* a date. (This is also assuming US date formats.) You
could use a formula such as ...

=0+("1-"&A2&"-2005")

Everything between the parenthesis is text and the "0+" part coerces it into
a numerical format. Quite lengthy discussing that in it's own right! So we
won't go there.. But you get the jist. From there it will put it in
numerical format. With the cell selected you can press Ctrl + 3 to set the
format to date, or Ctrl + 1 to manually select the date format from the Cell
Format dialog box.

With that formula in a cell, if you wish to make the value *stick* and not
be a formula anymore, select it and press Ctrl + C, then press Alt + E, S, V
(PasteSpecial | Values). This will take out the formula and leave only the
value of the cell.

HTH


--
Regards,
Zack Barresse, aka firefytr



"Todd Nelson" wrote in message
...
It is in text format. This is for my reviews and it pulls from somewhere
else on the sheet. Would a number/date format be easier to use?

"Zack Barresse" wrote:

Hi Todd,

That really depends if what you have in the cell is a date or text. When
you select a cell in question, what does it say in the Formula Bar? Is
it
"June"? Or is a number/date *formatted* to show June (mmmm)?

If it's a date, check out the EOMONTH function from the Analysis Toolpak.

HTH

--
Regards,
Zack Barresse, aka firefytr


"Todd Nelson" wrote in message
...
Is there a formula that would read the previous cell and insert the
following
month? Ex. Prev Cell is "June" the next cell would read "July"?

Hello Roger,

The only problem with that is the "June" is textual and not numerical, thus
erroring out when used with the YEAR or MONTH function (they look for
numerical values). You'll receive the infamous #VALUE! error for text in a
numerical equation.

--
Regards,
Zack Barresse, aka firefytr


"Roger Govier" wrote in message
...
Hi Todd
One way
=DATE(YEAR(A1),MONTH(A1)+1,1)
where 01/06/2005 is in A1

--
Regards

Roger Govier


"Todd Nelson" wrote in message
...
Is there a formula that would read the previous cell and insert the
following
month? Ex. Prev Cell is "June" the next cell would read "July"?

Hi Zack
I did say "when A1 is 01/06/2005"

--
Regards

Roger Govier


"Zack Barresse" wrote in message
...
Hello Roger,

The only problem with that is the "June" is textual and not numerical,
thus
erroring out when used with the YEAR or MONTH function (they look for
numerical values). You'll receive the infamous #VALUE! error for text in
a
numerical equation.

--
Regards,
Zack Barresse, aka firefytr


"Roger Govier" wrote in message
...
Hi Todd
One way
=DATE(YEAR(A1),MONTH(A1)+1,1)
where 01/06/2005 is in A1

--
Regards

Roger Govier


"Todd Nelson" wrote in message
...
Is there a formula that would read the previous cell and insert the
following
month? Ex. Prev Cell is "June" the next cell would read "July"?

Thank you both, now a question in relation to this. I do 6 month and yearly
reviews and I only want the information to pull during that time period. How
can I formulate this to pull either the last 6 months or 12 months? Is this
possible? Thank you again!!

"Zack Barresse" wrote:

Hello Roger,

The only problem with that is the "June" is textual and not numerical, thus
erroring out when used with the YEAR or MONTH function (they look for
numerical values). You'll receive the infamous #VALUE! error for text in a
numerical equation.

--
Regards,
Zack Barresse, aka firefytr


"Roger Govier" wrote in message
...
Hi Todd
One way
=DATE(YEAR(A1),MONTH(A1)+1,1)
where 01/06/2005 is in A1

--
Regards

Roger Govier


"Todd Nelson" wrote in message
...
Is there a formula that would read the previous cell and insert the
following
month? Ex. Prev Cell is "June" the next cell would read "July"?

On Fri, 5 Aug 2005 14:09:03 -0700, "Todd Nelson"
wrote:

Is there a formula that would read the previous cell and insert the following
month? Ex. Prev Cell is "June" the next cell would read "July"?

Assuming the content is text and is in A1, then:

=TEXT(DATE(2005,MONTH(DATEVALUE("1 "&A1&" 2005"))+1,1),"mmmm")

should return what you want in text format.


--ron