Month Formula #2

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"?
0
ToddNelson (21)
8/5/2005 9:09:03 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
234 Views

Similar Articles

[PageSpeed] 19

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" <ToddNelson@discussions.microsoft.com> wrote in message 
news:F87C070D-C3CD-4A69-9F22-E27D0295D8BF@microsoft.com...
> 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"? 


0
firefytr (112)
8/5/2005 9:13:45 PM
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" <ToddNelson@discussions.microsoft.com> wrote in message 
> news:F87C070D-C3CD-4A69-9F22-E27D0295D8BF@microsoft.com...
> > 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"? 
> 
> 
> 
0
ToddNelson (21)
8/5/2005 9:31:30 PM
Hi Todd
One way
=DATE(YEAR(A1),MONTH(A1)+1,1)
where 01/06/2005 is in A1

-- 
Regards

Roger Govier


"Todd Nelson" <ToddNelson@discussions.microsoft.com> wrote in message 
news:F87C070D-C3CD-4A69-9F22-E27D0295D8BF@microsoft.com...
> 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"? 


0
roger5293 (1125)
8/5/2005 9:36:27 PM
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" <ToddNelson@discussions.microsoft.com> wrote in message 
news:C33EBE1E-364C-4F4D-A682-C851C2AF9683@microsoft.com...
> 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" <ToddNelson@discussions.microsoft.com> wrote in message
>> news:F87C070D-C3CD-4A69-9F22-E27D0295D8BF@microsoft.com...
>> > 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"?
>>
>>
>> 


0
firefytr (112)
8/5/2005 9:44:27 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:O07O%23WgmFHA.1204@TK2MSFTNGP12.phx.gbl...
> Hi Todd
> One way
> =DATE(YEAR(A1),MONTH(A1)+1,1)
> where 01/06/2005 is in A1
>
> -- 
> Regards
>
> Roger Govier
>
>
> "Todd Nelson" <ToddNelson@discussions.microsoft.com> wrote in message
> news:F87C070D-C3CD-4A69-9F22-E27D0295D8BF@microsoft.com...
>> 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"?
>
> 


0
firefytr (112)
8/5/2005 9:46:02 PM
Hi Zack
I did say "when A1 is 01/06/2005"

-- 
Regards

Roger Govier


"Zack Barresse" <firefytr@vbaexpress.com> wrote in message 
news:u3LaTcgmFHA.2904@tk2msftngp13.phx.gbl...
> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:O07O%23WgmFHA.1204@TK2MSFTNGP12.phx.gbl...
>> Hi Todd
>> One way
>> =DATE(YEAR(A1),MONTH(A1)+1,1)
>> where 01/06/2005 is in A1
>>
>> -- 
>> Regards
>>
>> Roger Govier
>>
>>
>> "Todd Nelson" <ToddNelson@discussions.microsoft.com> wrote in message
>> news:F87C070D-C3CD-4A69-9F22-E27D0295D8BF@microsoft.com...
>>> 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"?
>>
>>
>
> 


0
roger5293 (1125)
8/5/2005 9:53:55 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message 
> news:O07O%23WgmFHA.1204@TK2MSFTNGP12.phx.gbl...
> > Hi Todd
> > One way
> > =DATE(YEAR(A1),MONTH(A1)+1,1)
> > where 01/06/2005 is in A1
> >
> > -- 
> > Regards
> >
> > Roger Govier
> >
> >
> > "Todd Nelson" <ToddNelson@discussions.microsoft.com> wrote in message
> > news:F87C070D-C3CD-4A69-9F22-E27D0295D8BF@microsoft.com...
> >> 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"?
> >
> > 
> 
> 
> 
0
ToddNelson (21)
8/5/2005 9:59:37 PM
On Fri, 5 Aug 2005 14:09:03 -0700, "Todd Nelson"
<ToddNelson@discussions.microsoft.com> 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
0
ronrosenfeld (3122)
8/6/2005 12:58:22 AM
Reply:

Similar Artilces:

formula for cell plus x amount of lines
Hi everyone. I need a bit of help with a specific formula. I have 2 spreadsheets, one is a working list of daily data with a weekly total, the other gives me a summary of the totals for each week. When Im inserting the formula for each weekly total, I can copy the line above (eg previous week) but excel automatically assigns the next line from my source sheet eg: =SUM(SALES!U1851) will become =SUM(SALES!U1852) What I want is a formula that says something like =SUM(SALES!U1851 plus 100 rows).. this giving me the formula of =SUM(SALES!U1951). Is there a code for this or will I have to chan...

Auto Updater #2
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I attempt to install the MS auto updater 2.1.1, I get a message that states my connection to the update server was interrupted, the server was too busy or there was a problem connecting with my network. Has anyone had this issue and how do I resolve this. On 4/6/09 3:07 PM, "dylan204@officeformac.com" <dylan204@officeformac.com> wrote: > When I attempt to install the MS auto updater 2.1.1, I get a message that > states my connection to the update server was interrupted, the server was > too...

UPC Equation #2
Okay so far i have figured out the sticky part of the equation. Steps 1-6..no problem. This is what i fgured out. Step One: Suppose you want to find the Check Digit for the UCC-12 (U.P.C.) Number 61414121022. Set up a table with 12 columns, and put the number 61414121022 into Positions One through Eleven. Position Twelve will be blank because it is reserved for the Check Digit. Step Two: Add the numbers in Positions One, Three, Five, Seven, Nine, and Eleven: Step Three: Multiply the result of Step Two by three: Step Four: Add the numbers in Positions Two, Four, Six, Eight, and Ten: Step...

workbook duplication #2
how do I duplicate a workbook with formulas so that it can be used for a different application, with the same formulas? i have saved and reentered data, however it keeps linking back to the original and data gets changed automatically. i have clicked on do not update. thanks, bruce Look at Edit/Links. Change the source for the new workbook as appropriate. On Tue, 12 Oct 2004 07:07:04 -0700, becamp <becamp@discussions.microsoft.com> wrote: >how do I duplicate a workbook with formulas so that it can be used for a >different application, with the same formulas? >i have saved ...

Applying Numbers to tickets, 2 sides. Is there a way to do it
I belong to a non-profit club which from time to time I make some raffle tickets (2 parts) Is it possible with publisher to number both sides of the tickets. The sheet prints roughtly 8 a page, and what I would lik eis the first ticket to print 001 on one side of the split ticket, and 001 on the other side. then number the 2nd one 002 and so on and so forth in numerical order till it is done? Thanks Bruce If the ticket number is on the front, you can insert the field twice, once on the left and the same on the right. If you plan to insert the number on the back you will need to s...

2 User Forms Comunicating?
Hi all, I had the intention to create a VBA program/script that: Step1. Calls a "FindAll" dialog (user can view and make the best choice out of the founded elements) Step2. When user closes the "FindAll" dialog, my UserForm1 should show up and run different Sub() using the last value that "FindAll" dialog selected. My problem is that I can't use the "FindAll" dialog because the focus is on the UserForm1 because it showed up lately. UNFORTUNATELY I CAN'T USE OTHER "FIND" DIALOG. IF ANYONE CAN HELP ME I WOULD APPRECIA...

Subcategories #2
I just upgraded to Money 2005 and find a useful feature is missing. I have been using Money for the last 7 years and enjoyed using a category and subcategory for my expenses. Now these two items are combined in one dropdown box and very hard to use. I used to use a category then tab to the subcategory and pick an item. Now when I pick a category the subcategories are not visible unless I use the mouse to scroll through. All previous versions gave an option of using one box or two but in the latest version I can't find how to get the two boxes back. Any ideas? Thanks. It's be...

Autofill Formulas
Hello. I have a workbook with 31 sheets of data. Each sheet is a form with a record of data. I would like extract the data into a list. For example, range A5 is the same field on each sheet, so I want to autofill a formula reference the same cell on different sheets. I suspect I need a macro for this, but wanted to make sure first. Can you autofill like this =Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5? -- Thanks, Mike =INDIRECT("Sheet" & (ROW()) & "!A5") Entered in A1 of new sheet. Copy down to A31............will increment the Sheet numbe...

A daily computation formula?
Is there a way to create a formula that adds "1" to a number every passing day? I have a spreadsheet that I use to calculate how late a company is on paying invoices, and I want to have a column that shows how many days past due an invoice is. Can this be done? CAN excel operate a forula that automatically calculates each passing day? please reply! Thanks! Jimmy Look at the today function A1 holds the due date, B1 holds either "Paid" or empty then this formula will tell you how many days behind =IF(B1="Paid","Paid","Vendor X is "&TEX...

Email Issue #2
I'm having an issue that I can't seem to get to the bottom of. I can send email without an issue except for when I'm emailing two doamins in practicular. Our setup is such that mail sent via exchange is sent our antispam/antivirus server using a smarthost and that server sends it out. According to the logs a reverse dns lookup fails as the reason the mail is being rejected. From some reserch this could happen if there is no pTR record created in our DNS which in our case is hosted by our ISP. Would tyou concurr that his would be a probelm. Some administrators have conf...

Update CRM From Rollup 2 to Rollup 3
Hi!.. I want to update MS CRM from rollup 2 to rollup 3. ¿What I need to update first, The server or the outlook clients? If the server is first updated, ¿Will the outlook clients (with rollup 2) still works with a rollup 3 CRM server?...This is because updating 50 clients may take a week and I want to know if the users will have problems untill we complete the update task. Thanks for your help... Daniel. ...

Junk Mail Folder #2
I am not sure if I should ask in Outlook or Exchange group, so I will ask in both. We have an Exchange 2003 Enterprise server, IMF is NOT installed. Our clients use Outlook 2003. I tried disabling Junk Mail folder using a GPO. Outlook displays the red X indicating it is indeed disabled. Problem: Mail still goes into the junk mail folder. How do I stop this. I already took out the GPO that disabled it and tried setting it at the client level, but that doesn't change the behavior. I looked everywhere for a reason for this, but absolutely no articles discussing the problem. ...

autoreply #2
Goodmorning, We are using Outlook2003 with Exchange server 5.5. Is it possible to make a rule with ''have server reply using a specific message '' with the original message? I want when: a message arrives to send a reply, always, but WITH the original message, otherwise people don't know on which message this reply is send. Thanks. John No. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "John Knijn" <john.knijn@p...

Outlook 2003 crash on startup in WinXP #2
My outlook has suddenly started crashing due on startup. It was working fine till a couple of days back. I haven't installed anything related to outlook except the normal update from Microsoft. I am using Outlook 2003 on XP. I tried the various solutions I could google in for but of no help. Here is the list of things(found in some post) I have already tried without any luck - Reinstall Outlook - Create a New Profile - Tried running outlook in Safe Mode (the safe mode is also crashing the same way as normal startup) - Remove outcmd.dat - Ran pstscan.exe on all my pst files. The Outlook...

2 insurance Health Insurance Quote Health Insurance Coverage
Everything on Health Insurance! Much information and services! Compare affordable health insurance plans side-by-side online. Free instant quotes. Choose, apply and buy online. Compare 4000 plans Great rates, full coverage Insurance to fit your life. A group of health insurance companies have launched a Web site where small-business owners can learn more about health savings accounts. They may not be living, breathing things (although at times they seem to have minds and wills of their own), yet our cars are similar to our bodies in the sense that we can maintain, and som...

two contact folders #2
In my outlook 2003, I maintained two contact folders. One of them is my personal one, the other is a shared one residing in a public folder (Exchange). What I would like to attain is that whenever I create a contact in my personal contact folder, this gets automatically created or copied also in the other shared contact folder. Do you have any suggestions on how to do that? Thanks in advance, Gianluca Bosco De�r Gianluca, this is not possible by default. Maybe a 3rd party synchronization software can support this: http://www.slipstick.com/outlook/sync.htm -- Oliver Vukovics Shar...

Nested ifs #2
Hi group Can anyone help - I need to show Pass / Fail column if test results in two columns meet two seperate criteria... Column A needs to have a result between -3.0 and +5.0% Column C needs to have a result between 95 and 105% The third column needs to show "PASS" if both conditions are met, otherwise "FAIL" I've tried to nest the ifs, but am getting the syntax wrong somewhere... Thanks - Marcie Hi try =IF(AND(A1>=-3,A1<=5,C1>=0.95,C1<=1.05),"Pass","Fail") >-----Original Message----- >Hi group >Can anyone help - I need to...

W-2 correction form
We need to correct a few W-2's that have already been sent out. We noticed the W-2 C forms have a different format and the amounts will not line up like the W-2's did. Does anyone have any suggestions? As you said, the formats are different. GP does not have any way to produce the W-2c form off the shelf. You either create a report that fits the form or complete the forms manually. Since you said it's just a few items, it would probably be a lot quicker to do it manually. -- Charles Allen, MVP "BFogle" wrote: > We need to correct a few W-2's that have...

how do I import messages from windows live to outlook 2
I have download outlook connector and its working. I have imported my contacts from windows live to outlook 2007 but cannot import the messages. How do I do this please. Windows Live is a free account. Have you tried exporting from Windows Live Mail? "Mike Cummings" <Mike Cummings@discussions.microsoft.com> wrote in message news:2E6E6ED6-F8C3-412A-A7CD-B6F3B46A6C25@microsoft.com... > I have download outlook connector and its working. I have imported my > contacts from windows live to outlook 2007 but cannot import the messages. > How do I do this please. Windows L...

access denied #2
I've searched these threads for info to no avail. I've got a new Win XP based computer running Outlook 2002 and I'm trying to get all of my mail from my old computer (Win ME, Outlook 2000) to the new machine. I am exporting to a .pst file on my hardrive and then burning it to CDR. I have unchecked the "read only" boxes in the file properties area prior to burning... but each time I try this (4 separate times now) I get the same message. ACCESS DENIED! (as has been mentioned in other threads) WTF?????? Please Help!!!! Thanks. Have you copied it from the CDR back t...

function/formula
Does anyone know of a book which lists and explains all the functions/formulas? Peter Nonley has a nice "dictionary" that defines all the worksheet functions with easy-to-follow examples. You can download the file for free here: http://homepage.ntlworld.com/noneley/ MVP Norman Harker also has one that you can download from MVP Debra Dalgleish's website: http://www.contextures.com/functions.html HTH Jason Atlanta, GA >-----Original Message----- >Does anyone know of a book which lists and explains all >the functions/formulas? >. > ...

CRM 1.2 Network Bandwidth usage
Does Microsoft already have a whitepaper for this? I know this was posted before, but was there a detailed explaination or a chart-form detailing the usage? On what structure or term or technology can CRM 1.2 run (acceptable) other than in an intranet? Can you please expound? Thanks :) For remote access I believe most customers will opt for a vpn solution so users in remote locations will open a vpn tunnel across the internet then launch crm as before. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "isalgado" <ian.salgado_remove_this_@gisimplement.com&g...

I need some help #2
What I need to be able to do is return a range of data based off of calculation. Here is an example of what I nee Widget 36.27 Gadget 31.27 Sprocket 43.35 Ok, I need to find all product that is 1.3 times higher percent, an .77 times lower in percent than Widget at 36.27 Basically return all values that are between 36.27*1.3 and 36.27*.7 and equal to 36.2 Can anybody help me do this Thanks Ilneva -- Ilneva ----------------------------------------------------------------------- Ilneval's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3116 View this threa...

2 or more columns on a report
I have a report that has only a couple of fields that it's pulling from, so when I print my report, there's not a lot there. It looks basically like this: Employee Name Avg. Score # Completed Doe, John 95 2 Smith, Mary 68 3 and so on. My question is this: Is there a way to put 2 groups of this info on the report page to condense the number of pages I have to print out? I'd like to report to look something like this: Employee name Avg. Score # Completed Employee Name Avg. Score # Completed ...

Dual Monitors #2
With Outlook 2000 I could open up 2 instances of Outlook, with for example the calendar on 1 screen and the inbox etc on the other and work with both With office XP version this doesnt seem possible - am I missing a trick. Help please. Right click on the folder you want opened and select Open in New Window. Move it to the monitor of your choice. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, pcasey1@-nospamplease-btopenworld.com...