Month Formula

T

Todd Nelson

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"?
 
Z

Zack Barresse

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
 
T

Todd Nelson

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?
 
Z

Zack Barresse

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
 
Z

Zack Barresse

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.
 
T

Todd Nelson

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!!
 
R

Ron Rosenfeld

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"?
Click to expand...

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
 
Top