Increase years by one formula

Hello all!  

I need help with a formula...

I have a date (1/1/04 but displayed as Jan-04) in cell E4.
I would like to have a formula that looks at E4 and returns the sam
date but increase the year by one 1.  Every formula I have trie
doesn't work or increases the month by one.  Any help would b
appreciated!
Cind

--
Message posted from http://www.ExcelForum.com

0
9/15/2004 7:57:17 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
305 Views

Similar Articles

[PageSpeed] 35

I'm sure there's a better way, but one way is:

=E4+366

>-----Original Message-----
>Hello all!  
>
>I need help with a formula...
>
>I have a date (1/1/04 but displayed as Jan-04) in cell E4.
>I would like to have a formula that looks at E4 and 
returns the same
>date but increase the year by one 1.  Every formula I 
have tried
>doesn't work or increases the month by one.  Any help 
would be
>appreciated!
>Cindy
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74718)
9/15/2004 8:11:23 PM
Hi Patti,

How many years have 366 days ?   (about 1 in 4)

instead use:
   =date(year(e4)+1,month(e4),Day(e4))

More information on Date and Time
   http://www.mvps.org/dmcritchie/excel/datetime.htm
   http://www.cpearson.com/excel/excel.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Patti" <anonymous@discussions.microsoft.com> wrote in message news:2ec301c49b60$2c8cfee0$a501280a@phx.gbl...
> I'm sure there's a better way, but one way is:
>
> =E4+366
>
> >-----Original Message-----
> >Hello all!
> >
> >I need help with a formula...
> >
> >I have a date (1/1/04 but displayed as Jan-04) in cell E4.
> >I would like to have a formula that looks at E4 and
> returns the same
> >date but increase the year by one 1.  Every formula I
> have tried
> >doesn't work or increases the month by one.  Any help
> would be
> >appreciated!
> >Cindy
> >
> >
> >---
> >Message posted from http://www.ExcelForum.com/
> >
> >.
> >


0
dmcritchie (2586)
9/15/2004 8:32:24 PM
Like I said, I'm sure there's a better way!  Thanks!

>-----Original Message-----
>Hi Patti,
>
>How many years have 366 days ?   (about 1 in 4)
>
>instead use:
>   =date(year(e4)+1,month(e4),Day(e4))
>
>More information on Date and Time
>   http://www.mvps.org/dmcritchie/excel/datetime.htm
>   http://www.cpearson.com/excel/excel.htm
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel    [site changed  
Nov. 2001]
>My Excel Pages:  
http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page:        
http://www.mvps.org/dmcritchie/excel/search.htm
>
>"Patti" <anonymous@discussions.microsoft.com> wrote in 
message news:2ec301c49b60$2c8cfee0$a501280a@phx.gbl...
>> I'm sure there's a better way, but one way is:
>>
>> =E4+366
>>
>> >-----Original Message-----
>> >Hello all!
>> >
>> >I need help with a formula...
>> >
>> >I have a date (1/1/04 but displayed as Jan-04) in cell 
E4.
>> >I would like to have a formula that looks at E4 and
>> returns the same
>> >date but increase the year by one 1.  Every formula I
>> have tried
>> >doesn't work or increases the month by one.  Any help
>> would be
>> >appreciated!
>> >Cindy
>> >
>> >
>> >---
>> >Message posted from http://www.ExcelForum.com/
>> >
>> >.
>> >
>
>
>.
>
0
anonymous (74718)
9/15/2004 8:41:49 PM
Reply:

Similar Artilces:

emailing just one worksheet/tab of workbook
Is it possible to send only one worksheet/tab in an e-mail or is the entire workbook the only option? Our school district is using Excel 2007 and our secretary would like to be able to send only certain worksheets to teachers. In article <053e56bd-0f01-4ebf-8a98-deb93c3cbdbe@z66g2000hsc.googlegroups.com>, Michelle <teacher24_70@yahoo.com> wrote: >Is it possible to send only one worksheet/tab in an e-mail or is the >entire workbook the only option? Our school district is using Excel >2007 and our secretary would like to be able to send only certain >worksheets to teac...

GP 9.0 Year-end and Variable Allocation Accounts
This year we defined variable allocation accounts which have worked well for us. However, we find at year-end that, after setting up the new fiscal year, we cannot use these accounts to post into the new year. And, once the previous year is closed, we cannot use these accounts to back-post into the historical year either. We need one or the other of these capabilities. Q1 - Why does GP impose this restriction and Q2 - is there a workaround that can be suggested? Thanks. -- BizTech I cannot answer Q1, but for Q2, the workaround would be to not close the GL until you have all trx p...

Formatting Cells to display Date: month/day/year
I am a newbie when it comes to Excel and after doing some searchin through threads I couldn't find the exact answer I needed so her goes: [image: http://www.geocities.com/danmann81/timesheet.jpg] The red box is the area I'm trying to format. What I want to do is se the formula for dates so that the dates update automatically withou having to change them manually every pay period. I hope that mak sense. Thank You Danie -- Message posted from http://www.ExcelForum.com Hi your link does not work :-) You may try to avoid attachments/links and describe your issue in plain text ...

Multiple charts on one worksheet?
Hi, I've seen this subject posted before but could not work out my problem with the answers to those posts, so I'll start my own :) I wrote a VB program that invokes Excel and reads data into it (ADO), selects the appropriate range, then creates a chart on that worksheet. I then copy that chart to a new worksheet named "Charts" and then run through the process a number of times depending on the number of elements a user has asked for (stock symbols). It does this fine except it places the charts one on top of the other on the "Charts" sheet. I've tried using ...

I need a formula to sum column b if column a is between two dates
I have an excel spreadsheet with employees time off. I need a formula that will add column b if column a is betwee two dates. For example: if column a is between 9/22/04 and 9/21/05 then add column b. I have tried all different formluas but can't get this to work. ...

Increase size of pie chart within control?
Is there a way to increase the size of a pie chart within its control? I have a limited amount of space in which to display the chart, so the overall size of the control is restricted. But within it there's quite a lot of "wasted" white space and I would like to make the pie itself larger and more legible. Can do, or not? Many thanks CW In design mode, double-click on the chart within the control to select it for editing. Then use the sizing handles to resize the chart area to the size of the control; this does not have to be square. Click on the plot area to select it ...

Year end close with 1 database and 5 entities
I have one data base with 5 entities. Each entity has its own Retained Earnings account. MGP only allows one Retained Earnings account to be used when closing the year. I currently now must do a journal entry to reclass each entities Current Year activity out of that one Retained Earnings account and to its own Retained Earnings account. I have been using 12/31/yy as the date of the reclass entry. It however creates an out of balance for each entity's Balance Sheet. The out of balance amount is the Current Year's activity. So: 1. Does anyone else have this issue? If you...

Drivers for percentage increase
I am trying to set up a driver to include in my budget profile so that my managers can select the percentage from a driver and see on each budget line what the outcome will be i.e staff costs may be calculated at 150,000 but if I used my driver to calculate an increase of 3.5% that would change the staff costs to 155,250. Can anyone help? Regards Jo I am not sure what you question is but this may help:- In cell A 2 I have 150,000 In cell B 2 I have 3.5% In cell C 2 I have:- =(A2*B2)+A2 - and this returns 155,250 to cell C 2. If my comments have helped please h...

Does any one have a reciept template for excel?
I am looking for a reciept template for excel. A loan repayment reciept actually. Try this link... http://office.microsoft.com/en-us/templates/default.aspx "BJ" wrote: > I am looking for a reciept template for excel. A loan repayment reciept > actually. ...

MTD Formula Help!
I'm trying to set-up a spreadsheet which contains seven columns A) Date B) # of Invoices Generated C) Daily Sales D) Average dollars per Invoice E) MTD # of Invoices F) MTD Sales G) MTD Average Invoice My question: What formula can I use for columns D-E & F ? I want to run MTD totals. Not sure how to accomplish this task with out populating all the dates with the last total i.e. if it's the 15th the 16th through the 30th are the same total. Please Help! You can check for an entry in the Sales column. For example in cell D2: =IF(C2="","",C2/B2) In...

to increase quantity
hello everybody, i have a question: i want to use project to calculate the delivery time of a machine component. I know the bill of material and the time of every step (manufacturing, mounting, etc.). I don't have problem to create a gantt for this item: but i have problem when i want to create a gantt for a number greater than one of my item. I think that i have to use a function that increase the duration of each step of the process, but i don't know how. Please help me. thank you -- sentenza ------------------------------------------------------------------------ s...

Counting Results of Formulas
I have a column in a spreadsheet that contains the result of a a lookup function (refering to values in another spreadsheet) and displays a blank field if the lookup does not find a match in the second spreadsheet. I need to count how many matches are found (or not found). But when I use the count function, it seems to be counting the formula and not the result of the formula. Does this make sense? Example: Formula of cells B10:B1500 = IFERROR((VLOOKUP(C45,'[Completed Property List.xls]Sheet1'!$A$2:$B$10000,2,FALSE)),"") - displays the contents of ma...

Formula value does not show-"error-constant in formula"
I can't fix a simple Concatenate formula that won't display it's result (tried turning Formula Auditting mode on & off). When I do an "Evaluate Formula" on the cells that formulas are not displaying values in I get the following error: The cell currently being evaluated contains a constant". The formual looks identical to ones in cells above and below (except for cell #'s that do work fine. The data referenced in the formula looks fine. This one works: =CONCATENATE("SHOULDER BOLT ", A6," DIA X ",B6," L X ",H6,) T...

Unhide more than one worksheet at a time
I have a worksheet that contains 50 hidden worksheets. Is there a method or macro that somebody knows of where I can unhide more than one workseet at a time? Thanks, Dave =@===----¬----¬----¬.¸¸.·´¯`·.¸¸.·´¯`·.¸.·<º))))>< Dave, Try the following code: Sub AAA() Dim WS As Worksheet For Each WS In ActiveWorkbook.Worksheets WS.Visible = True Next WS End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "DBavi...

Multiple Users on One Windows Logon / Autodelete of read messages
Hello, I am having two problems in a new office environment. 1. Everyone is using the same Windows logon (only one user accout) while at the same time trying to use their individual email accounts in Outlook. I found that when I added an Outlook profile for myself that I see multiple users incoming mail including my own. Is the best fix for this is to create seperate Windows logon profiles for everyone or is there some other way to handle it? 2. Maybe this is a seperate issue, but every time I open an incoming message (addressed to me or one of the other users), it is AUTOMATICALLY DELETE...

Multiple Windows on One LArger Panel( Window)
Hi! All, I have a requirement to implement a large Panel( Main Window) which is an are on top I want to locate subsequent windows. e.g. 1. A Live Communications Client Launch Window. 2. When the user selects the A/V session , I want to draw the A/V Session Window side by side located on the Main Window along with the Live Communication Launch Window. 3. If the user selects to Chat I want the Chat window to show up on the Lower left Bottom of the Main Window. etc... Note: the Subsequent window each should be a window with the Menu Bar etc for this reason these Windows can not...

Date Increase
I currently have a form for entering current passwords for systems. I have a field to enter the date the password was last changed, and another field for the date the password expires, which is every 30 days. Is there anyway i can set the date password expires field to update automatically? The 2nd field is just calculated from the 1st and better left as a calculation in a query rather than a field in a table. There are no triggers at the table level in Access. Dan Wood wrote: >I currently have a form for entering current passwords for systems. I have a >field to enter...

Number of the day of the year
I need a formula that will look at a date and tell me what number day it is in that year. eg 31/1/2007 would show as 31. 1/3/2008 is 61. It is sort of like a julian date but without the year part of the number. Thanks Try the below with date in cell A1 =DATEDIF(DATE(YEAR(A1),1,0),A1,"d") -- Jacob (MVP - Excel) "NDBC" wrote: > I need a formula that will look at a date and tell me what number day it is > in that year. eg 31/1/2007 would show as 31. 1/3/2008 is 61. It is sort of > like a julian date but without the year part of the numbe...

Restoring a DC over a year old, Event 8012.
Hi, I never usually have any issues cloning a curretn DC, however I need to restore one that is over 1 year old for our Law team. I think it has something to do with a tomestone of 60 days on the System State, what can I do? I did think about changint the servers clock before restoring. Hello Whiteford, Is that the only DC in the network? Basically you will loose all configuration since then and also all computers have to be readded to the domain, because they loose there machine password. If you have additional DCs, forget the restore that way and better install ...

can I increase excel column numbers to more than 256?
I am trying to analyse tables including more than 256 variables. I cannot transpose them into rows because the program i am using in association with excel (xlstat) takes the columns as the variables. Is there a way to increase the number of the columns?? or is it a fixed specification of the excel worksheet? thank for any help! Yes, it is fixed - you cannot increase the number of columns. You can, of course, split your data up into chunks of 250 columns - the first block of data on row 1, for example, then the next set starting on another row etc. Pete ...

Transform a Cell from Formula to Number
Is there a way to turn a cell from a formula to the number the formula outputs? Copy it to the clipboard, then do Edit>PasteSpceial, click Values -- HTH Bob Phillips "Sloth" <Sloth@discussions.microsoft.com> wrote in message news:76DA206E-CFC3-4E63-9789-69735538E1A7@microsoft.com... > Is there a way to turn a cell from a formula to the number the formula outputs? Copy the cell. Edit/Paste Special/Values. In article <76DA206E-CFC3-4E63-9789-69735538E1A7@microsoft.com>, "Sloth" <Sloth@discussions.microsoft.com> wrote: > Is there a way to...

Formatting does not follow when copying from one cell to another
I have a cell on sheet 1 that I have added text to with some bolding, coloring of text, etc. On Sheet 2 I have a cell that I have told to copy whatever is in the cell on sheet 1 described above. Why doesn't the formatting copy over too?? A formula returns a result, not any formatting. Pete On Apr 7, 9:55=A0pm, Banker <Ban...@discussions.microsoft.com> wrote: > I have a cell on sheet 1 that I have added text to with some bolding, > coloring of text, etc. On Sheet 2 I have a cell that I have told to copy > whatever is in the cell on sheet 1 described above. =...

Multiple logins
Here is the scenario: My Fiancee & I share some of the same accounts. When she logs into the bank, she can see our joint accounts plus her personal accounts. The same for me. The issue is when I set Money up to pull from the bank, I get two of the same account. If I try to delete one of the duplicates, it tells me it has to delete all accounts from that login (including the history). I do not want to do this, but what can I do to save my sanity? Thanks! ...

Excel formulas imported from tab-delimited files
I am trying to import some tab delimited entries into an Excel spreadsheet. Everything is working fine and dandy except that the formula fields which reference other worksheets in the spreadsheet are not getting automatically resolved unless I double click on them. For example, the "=Employee_Info!Y6" entry is not automatically getting resolved unless I double click on that value. Formulas that do not reference other worksheets are resolving correctly, without any intervention. Can someone kindly suggest how I can get past the problem? Thanks, Nimmi I'm not sure why excel ...

Multiple emails w/ same tracking token in one activity?
Is it possible to track an ongoing email conversation as a single activity (assuming tracking token is present and accurate), instead of having each outgoing and incoming email result in a separate activity? One troubleshooting conversation with a contact can end up creating 12 activities in their history. If anyone knows the answer and/or can tell me how to do so, please let me know. Thanks! Afraid not. Each of those seperate emails is a seperate "activity" and thus is tracked seperately. -- Matt Parks MVP - Microsoft CRM "SCAB" <SCAB@discussions.microso...