Probably very easy question conerning auto-changing dates

I've got a list of dates in column B. Row 1 is labeled "date", Row 2 starts
my date data (May-04). I want a list in column B of the preceding 23 months,
so columb B ends with Jun-02. Obviously, I can manually input this
information. What I can't figure out is how if I go and change the first
date (say, to Jun-04) it will auto-change my list of dates accordingly (so
my list would now end in Jul-02).

NOTE: I know how to create a handle-fill, but it won't update any changes I
make to the dates, nor can I get it to go backwards, chronologically.

Thanks,
Jacob
jfarino@amerisave.com


0
jfarino (12)
6/29/2004 6:32:35 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
577 Views

Similar Articles

[PageSpeed] 56

Jacob,

The fill handle will go backwards, but only if you drag up or left.  Select
the first two, then use it.

Or use this formula, and copy down with fill handle:

=DATE(YEAR(B2),MONTH(B2)-1,DAY(B2))

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jacob" <jfarino@amerisave.com> wrote in message
news:Oz23hbgXEHA.212@TK2MSFTNGP12.phx.gbl...
> I've got a list of dates in column B. Row 1 is labeled "date", Row 2
starts
> my date data (May-04). I want a list in column B of the preceding 23
months,
> so columb B ends with Jun-02. Obviously, I can manually input this
> information. What I can't figure out is how if I go and change the first
> date (say, to Jun-04) it will auto-change my list of dates accordingly (so
> my list would now end in Jul-02).
>
> NOTE: I know how to create a handle-fill, but it won't update any changes
I
> make to the dates, nor can I get it to go backwards, chronologically.
>
> Thanks,
> Jacob
> jfarino@amerisave.com
>
>


0
nowhere1083 (630)
6/29/2004 6:54:03 PM
I think I found the answer to my own question, though I have to believe
there has to be an easier way to accomplish the same thing.
If you put a date into your starting cell B2=(may, 01, 2004), in the
following cells, simply put the folling formula =B2-1, B2-32, B2-63, etc...
You have to enter it into each cell, and it will deduct that amount of days,
effectively changing the date backwards that number of days. Tedious, to say
the least, and it won't hold up forever, because eventually the odd-number
of days in each month will catch up to your -X days and throw off the
months. But it should do for quite a while anyway.
If someone has a more accurate (and faster) method, please post or email
me!!

-Jacob
jfarino@amerisave.com

"Jacob" <jfarino@amerisave.com> wrote in message
news:%23RZrBLhXEHA.2664@TK2MSFTNGP09.phx.gbl...
> Okay, the reverse handle worked, I can get it to go the correct way as far
> as dates are concerned, but the auto-changing/updating the dates if one is
> changed still doesn't work...
>
> The formula gives a circular logic error. Any suggestions?
>
>
>
> "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
> news:OOl41pgXEHA.1048@tk2msftngp13.phx.gbl...
> > Jacob,
> >
> > The fill handle will go backwards, but only if you drag up or left.
> Select
> > the first two, then use it.
> >
> > Or use this formula, and copy down with fill handle:
> >
> > =DATE(YEAR(B2),MONTH(B2)-1,DAY(B2))
> >
> > -- 
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "Jacob" <jfarino@amerisave.com> wrote in message
> > news:Oz23hbgXEHA.212@TK2MSFTNGP12.phx.gbl...
> > > I've got a list of dates in column B. Row 1 is labeled "date", Row 2
> > starts
> > > my date data (May-04). I want a list in column B of the preceding 23
> > months,
> > > so columb B ends with Jun-02. Obviously, I can manually input this
> > > information. What I can't figure out is how if I go and change the
first
> > > date (say, to Jun-04) it will auto-change my list of dates accordingly
> (so
> > > my list would now end in Jul-02).
> > >
> > > NOTE: I know how to create a handle-fill, but it won't update any
> changes
> > I
> > > make to the dates, nor can I get it to go backwards, chronologically.
> > >
> > > Thanks,
> > > Jacob
> > > jfarino@amerisave.com
> > >
> > >
> >
> >
>
>
>


0
jfarino (12)
6/29/2004 8:39:30 PM
Jacob,

The formula as written goes into B3.  Then copy down with the fill handle.
=DATE(YEAR(B2),MONTH(B2)-1,DAY(B2))
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jacob" <jfarino@amerisave.com> wrote in message
news:epATaihXEHA.2388@TK2MSFTNGP11.phx.gbl...
> I think I found the answer to my own question, though I have to believe
> there has to be an easier way to accomplish the same thing.
> If you put a date into your starting cell B2=(may, 01, 2004), in the
> following cells, simply put the folling formula =B2-1, B2-32, B2-63,
etc...
> You have to enter it into each cell, and it will deduct that amount of
days,
> effectively changing the date backwards that number of days. Tedious, to
say
> the least, and it won't hold up forever, because eventually the odd-number
> of days in each month will catch up to your -X days and throw off the
> months. But it should do for quite a while anyway.
> If someone has a more accurate (and faster) method, please post or email
> me!!
>
> -Jacob
> jfarino@amerisave.com
>
> "Jacob" <jfarino@amerisave.com> wrote in message
> news:%23RZrBLhXEHA.2664@TK2MSFTNGP09.phx.gbl...
> > Okay, the reverse handle worked, I can get it to go the correct way as
far
> > as dates are concerned, but the auto-changing/updating the dates if one
is
> > changed still doesn't work...
> >
> > The formula gives a circular logic error. Any suggestions?
> >
> >
> >
> > "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
> > news:OOl41pgXEHA.1048@tk2msftngp13.phx.gbl...
> > > Jacob,
> > >
> > > The fill handle will go backwards, but only if you drag up or left.
> > Select
> > > the first two, then use it.
> > >
> > > Or use this formula, and copy down with fill handle:
> > >
> > > =DATE(YEAR(B2),MONTH(B2)-1,DAY(B2))
> > >
> > > -- 
> > > Earl Kiosterud
> > > mvpearl omitthisword at verizon period net
> > > -------------------------------------------
> > >
> > > "Jacob" <jfarino@amerisave.com> wrote in message
> > > news:Oz23hbgXEHA.212@TK2MSFTNGP12.phx.gbl...
> > > > I've got a list of dates in column B. Row 1 is labeled "date", Row 2
> > > starts
> > > > my date data (May-04). I want a list in column B of the preceding 23
> > > months,
> > > > so columb B ends with Jun-02. Obviously, I can manually input this
> > > > information. What I can't figure out is how if I go and change the
> first
> > > > date (say, to Jun-04) it will auto-change my list of dates
accordingly
> > (so
> > > > my list would now end in Jul-02).
> > > >
> > > > NOTE: I know how to create a handle-fill, but it won't update any
> > changes
> > > I
> > > > make to the dates, nor can I get it to go backwards,
chronologically.
> > > >
> > > > Thanks,
> > > > Jacob
> > > > jfarino@amerisave.com
> > > >
> > > >
> > >
> > >
> >
> >
> >
>
>


0
nowhere1083 (630)
6/30/2004 3:03:10 AM
Reply:

Similar Artilces:

Probability
Hi Everyone, please help me solve this 20 people are invited to pick a random number between 1 and 100. Fin the probability that, out of the 20 random numbers selected, that ther are exactly 18 different numbers. Thank you so much for solving i -- Message posted from http://www.ExcelForum.com Hi is this homework :-) >-----Original Message----- >Hi Everyone, please help me solve this > >20 people are invited to pick a random number between 1 and 100. Find >the probability that, out of the 20 random numbers selected, that there >are exactly 18 different numbers. > &g...

OWA server Question.
Can one OWA server serve multiple sites or do we need separate OWA servers for different sites? Thanks, Sheila de Dios Depends what you mean by "sites". It can host multiple websites and handle multiple domain names. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Sheila de Dios" <sdedios@techspan.com> w...

Changing vat rate calculation in Money 2006
I have enabled VAT calculations on my transactions in my accounts and the tax rate has changed but I can't find a way to change it in Money 2006. This is a database that has been upgraded over many versions and I'm sure I've changed this before in another version. I can see the old rate in some reports I print but for the life of me I can't find a setting in 2006. Anyone know of the way to change the Vat Rate? or is this another new feature ;(( -- Boyd In microsoft.public.money, Boyd Tucker wrote: >I have enabled VAT calculations on my transactions in my accou...

How can I change the color of negative bubble in bubble chart
I find it impossible to change the color of negative bubble into any color other than the default white. Could anyone tell me how to change, or it is not changable? Thanks! Double click on the series, on the Patterns tab, click Fill Effects, on the Gradient tab, choose the Two Colors option, then select what you want for color 2 (color 1 is what you already selected for the series color). Click OK, then on the Patterns tab, select again the color you want for the positive bubbles (wiping out the gradient in the Sample box). Click OK. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutori...

Message question
I have a custom list control and some other controls on my dialog box. Every time when I select item on the custom list control. I always receive the OnRButtonDown() message which is what I want. But I also need OnNMClickCustomList() which fired also. I add NM_CLICK handler OnNMClickCustomList() on my dialog. But It is NOT fired every time. If I select different item and keep the custom list control focused. I can get the NM_CLICK every time When I click the item; But If I select item A on the custom list control and click other control on the dialog, then click one of the item in the custom...

more best practice questions
Hi, new admin, inherited an Exch 2k3 environment, 3 servers, non-clustered, no service packs, poorly designed and managed by previous admin. :-( So here goes.... I want to reduce message sizes globally from 100 MB to 10 MB. This should be a no-brainer but I just want to know if there are any gotchas that might happen as a result of this. I would like to stop OOO replies outbound, but management needs convincing - afraid it might upset our customers too much. Any thoughts? Is it worth the fight? I am building a test environment to test SP2 and AV upgrades, plus DR testing. Is ther...

5.5 to 2003 Migration questions (mainly PF related)
Good-day, I am currently in the middle of a test migration from 5.5 on an NT 4.0 member to Exchange Server 2003 on a Win 2003 DC running in 2003 native mode. I've followed the 2003 deployment guide but have a few questions at my present point in testing, hopefully some who have been down this road could comment: 1 - Public Folder Replication vs Move With mailboxes it's quite clear - within the site I can just move them to the new server. With PF, what is the difference between setting up replication (which I seem to have done now via ADC and looks to be working) or using th...

A CTreeCtrl border question
Hi there, For some reason, I need to create a tree control on a parent window by calling its Create function directly. I do it like this: CMyTreeCtrl *pTree = new CMyTreeCtrl(); ... pTree->Create(...); Everything is OK except that the control doesn't have its border. Then I made a search on the Internet, and found I need to draw the border myself. I created a WM_PAINT message handler where I called dc.DrawEdge(...) function to draw the border. The border was created properly, but then the problem is that the tree nodes disappeared. It doesn't seem that OnPaint is not th...

A question on joining more than one xml document to xsl file??
How do it bind the articlelink nodes in the layout.xsl?? I have several xml pages(art1.xml, art2.xml, so on) that I know how link them up via articles.xml, <?xml version="1.0" encoding="utf-8"?> <articles> <articlelink href="art1.xml"/> <articlelink href="art2.xml"/> ...... </articles> But the layout.xsl page is this: <?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="html&quo...

Datagram sockets and compatibility
Here's a quote from the description of the CHATTER example "CArchive does not support datagram sockets, so all data translation would have to be handled by the program itself, or the user could only chat with those who are using the same version of CHATTER. By using the stream sockets, users of Windows 95, Windows NT ANSI, and Windows NT Unicode versions can chat with each other without concerns of version compatibility. Is there a difference in the way a UPD packet is sent/received depending on which version of Windows is being used? What are the concerns of version compaibility ...

Changing outlook folder from IMAP to "mail and post"
I've got a small problem, I've got some users who keep there mail on our exchange server and using Outlook XP they can view there mail fine. But when they try to use OWA some folders don't show, they only get garbage. The only thing I can see incommon with the folders that won't show and the ones that do is the one that don't when I click on the properties of the folder in Outlook it shows the type as "IMAP" and the ones that do work as "Mail and Post". Is there a way to change the folder time with out manually creating a new folder, moving the mess...

OL2003
How can you devise a rule to catch junk email sent to you that is disguised by using your own email address (associated with some bogus senders name)? ...

Question about DaoRecordset
I have a form with 3 combo boxes on it. cbFields - which lists all the fields in a table. cbCondition - which lists all the different conditions, e.g. =, <>, > etc. and the third will list all the distinct values in the field chosen from cbFields. because this list of all the possible values from could come from any field in the table, I can't create a CDaoRecordset with a DoFieldExchange method because I don't know what field in the field to read the values in from. So how would I handle this? Try these steps: 1) Create a dummy table that contains all the field types...

ExMerge Question #6
I have tried several times now to use this tool, but somewhere along the lines I keep getting errors. Below is my log file. Perhaps someone can help me figure out why I can't use a tool that obviously everyone else has no problem with! Info - Win2003R2, Exch2003 SP2 **************************************************************** Microsoft Exchange Mailbox Merge Program, v6.5.7529.0 Start Logging:August 07, 2006 11:24:34 **************************************************************** [11:24:34] Logging Level: None [11:24:34] Reading settings from file 'D:\Program Files\Exchsrv...

How to modify principal without changing payment amount
I am having a hard time figuring out how to change the principal payment amount without changing the total payment amount. I am using Money 2005, and have all my loan details set up correctly (to my knowledge). I have recurring payments set up in my bills, and the total amount of the loan payment is $100. The problem is that Money puts $100 toward the principal which is not correct. I dont have a problem modifying the transactions after the fact manually, but I can't seem to even do that. I can double click on the transaction to modify it, but as soon as I modify the principal ...

Few questions
It is exam results day in the UK today and so I am trying to analyse our results and need some help. Columns A & B = person info Column C = Another number (see further down) Column D = Tutor Column E = Class Column F = Target Grade Column G = Mock Exam Grade Column H = Actual grade. Now within certain ranges within these columns, for example rows 1:30, I want to do the following:- - "Work out the percentage of grade A*-C grades for that group" - each entry in column H has a grade ranging from A*, A, B,C,D,E,F etc.... I want to work out how many of the letters in column H are a...

If/Then Question
Because I have no idea what to call this since I am a newbie with Excel, my searches in google did not work. Hopefully, I can explain what I need without coming off sounding like an idiot... hell, I didn't even know what to put in the subject line. Anyway... I run an online NCAA Football league and we award points to coaches for their successes, but with 119 teams, keeping track can be a real pain. Thus, I am trying to make it as easy as possible through Excel. Wthin the sheet, their are a number of variables, but this is what I am focusing on. We award 5 points for a victory. As it ...

Question about Paste Special
Hi, I have a problem sometimes with the paste special options when goin from one excel workbook to another. For example, sometimes when I cop data from one workbook and then paste special into another, I get th options that include: All, formulas, values, formats, has the option t transpose the data and paste link among other options. And the sometimes I try and paste data to another workbook and I end up th paste special options: Bitmap Image Object, picture, bitmap, and I als lose the ability to paste link. Well you can do it, but it puts it i as an object. What I want is the first past...

IE8 privacy question
I am running XP-Pro SP3 and considering upgrading from IE7 to IE8. I have read all the feature and benefit articles from MS, but have one question that remains unanswered. In IE8 there is mention of being able to restore previously viewed websites or something to that effect. I think this is on a drop down menu somewhere. 1) Does In-private viewing prevent this? 2) Does manually deleting your complete browsing history clear this? 3) Is there a registry entry that can prevent this action? 4) Is there a group policy change that can prevent this action? In essence for privacy pur...

IF AND question
Hi there, I need a function that can provide one of three answers: 1 2 1 1... 3 3 4 4... ? ? ? ?... If A1 = 1 and A2=3, answer 106; but if A1=1 and A2 = 4, answer 104; but if A1=2, regardless of A2, answer 95. The next function for column B is the same, except the answer is dependent on the value delivered from the column A function. E.g. A3 + 6 or A3 +4 or A3-5 etc. Any help would be most appreciated. -- ***** Many thanks Gamq Use the below formula for your first query. =IF(A1="","",IF(A1=2,95,IF(AND(A1=1,A2=3),106,IF(AND(A1=1,A2=4),104)))) ...

Print titles
The "print titles" option under page setup/sheet is grayed out. How can I get it to work since I have multiple sheets that I need to carry over the titles when I print out the sheets? The titles I need are not in the first column or row. I see how it is done if it was available. GA -- My address is spoofed, so do not reply directly. If you are accessing Page Setup from the Print Preview screen, thos options will be grayed out. You need to access it from the main menu File>Page Setup | Sheet. Does this work for you -- swatsp0 -------------------------------------------...

Field lookup query question
Access 2003. I have two tables. Master and IssuePart. Each table has a report number (Master.OA) that ties all this together. I wanted to make the Master table perform a lookup by matching the report number in IssuePart and bringup a listbox. But the query won't cooperate. I can manually enter a report number and get the results I want, but as soon as I try to change the criteria to the Master report number field name, it chokes with syntaxs errors and such. Here's the SQL the Builder produces. SELECT IssueData.[Part Number], IssueData.[Serial Number] FROM IssueData WHERE ((ISS...

"Send To" question
Using WindowsXP explorer, select a file, right click -> Send To -> Mail Recipient. Outlook launches new mail message with attachment & a default message: The message is ready to be sent with the following file or link attachments: <attached filename> Note: To protect against computer viruses, e-mail programs may prevent sending or receiving certain types of file attachments. Check your e-mail security settings to determine how attachments are handled. How to remove or change this message? Searched all of C:\ for text string ´┐ŻNote: To protect against comput...

I could probably look this up but
Hi, I could probably find this somewhere but end of year financials am really tired and this will probably get me an answer before I can find the solution. I copied a sheet from another book to use in the new year financials but now every time I open the new book it asks if I want to update the linked book. I have to answer "no" every time. How do I copy a sheet from an old book in the ended financial year and use it in the new year without this happening? and can I turn off the link somehow in the already copied sheet? Thanks to anyone who can help. Mel In Excel 2003 ...

RMS User Question/Problem
Hello, I Downloaded and installed Microsoft Dynamics RMS, but when I first run it (for the first time) it asks me user ID and password, but have not yet established one, and is the first time I run the program! (Or is it comes with some standard user / default?) User ID: 1 Password: password Robert Armstrong RMS Systems Inc. "F.Misle" <F.Misle@discussions.microsoft.com> wrote in message news:B29A28E5-315C-4A1A-9490-A497651736A0@microsoft.com... > Hello, > I Downloaded and installed Microsoft Dynamics RMS, but when I first run it > (for the first...