Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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"? |
#2
|
|||
|
|||
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"? |
#3
|
|||
|
|||
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"? |
#4
|
|||
|
|||
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"? |
#5
|
|||
|
|||
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"? |
#6
|
|||
|
|||
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"? |
#7
|
|||
|
|||
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"? |
#8
|
|||
|
|||
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"? |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for # of sales days in a month? | Excel Worksheet Functions | |||
Calculating days in a month | Excel Discussion (Misc queries) | |||
Date formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
dynamic year to date formula | Excel Worksheet Functions |