Date formula #8

How do you put in a formula for a date to change with corresponding number of 
years added to it?

example.

column 1 is # of years
column 2 is date
column 3 is calculated current date (# of years added to date)
0
Confused1 (124)
10/19/2004 4:03:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
333 Views

Similar Articles

[PageSpeed] 51

=date(Year(A2)+A1,Month(A2),Day(A2))

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Confused" <Confused@discussions.microsoft.com> wrote in message 
news:060D5867-359A-42C4-A907-6418405B4B22@microsoft.com...
> How do you put in a formula for a date to change with corresponding number 
> of
> years added to it?
>
> example.
>
> column 1 is # of years
> column 2 is date
> column 3 is calculated current date (# of years added to date) 


0
nicolaus (2022)
10/19/2004 4:10:44 PM
Hi

C2=DATE(YEAR(B2)+A2,MONTH(B2),DAY(B2))

To cover cases when date falls on 29 February of leap year, use formula
C2=MIN(DATE(YEAR(B2)+A2,MONTH(B2),DAY(B2)),DATE(YEAR(B2)+A2,MONTH(B2)+1,0))


Arvi Laanemets


"Confused" <Confused@discussions.microsoft.com> wrote in message
news:060D5867-359A-42C4-A907-6418405B4B22@microsoft.com...
> How do you put in a formula for a date to change with corresponding number
of
> years added to it?
>
> example.
>
> column 1 is # of years
> column 2 is date
> column 3 is calculated current date (# of years added to date)


0
10/19/2004 4:10:46 PM
I mixed op rows and columns. Of course Arvi is right.

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:uxmXrYftEHA.376@TK2MSFTNGP09.phx.gbl...
> =date(Year(A2)+A1,Month(A2),Day(A2))
>
> -- 
>
> Kind Regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "Confused" <Confused@discussions.microsoft.com> wrote in message 
> news:060D5867-359A-42C4-A907-6418405B4B22@microsoft.com...
>> How do you put in a formula for a date to change with corresponding 
>> number of
>> years added to it?
>>
>> example.
>>
>> column 1 is # of years
>> column 2 is date
>> column 3 is calculated current date (# of years added to date)
>
> 


0
nicolaus (2022)
10/19/2004 4:30:35 PM
Thank you very much! Where do you guys learn this stuff?

"Arvi Laanemets" wrote:

> Hi
> 
> C2=DATE(YEAR(B2)+A2,MONTH(B2),DAY(B2))
> 
> To cover cases when date falls on 29 February of leap year, use formula
> C2=MIN(DATE(YEAR(B2)+A2,MONTH(B2),DAY(B2)),DATE(YEAR(B2)+A2,MONTH(B2)+1,0))
> 
> 
> Arvi Laanemets
> 
> 
> "Confused" <Confused@discussions.microsoft.com> wrote in message
> news:060D5867-359A-42C4-A907-6418405B4B22@microsoft.com...
> > How do you put in a formula for a date to change with corresponding number
> of
> > years added to it?
> >
> > example.
> >
> > column 1 is # of years
> > column 2 is date
> > column 3 is calculated current date (# of years added to date)
> 
> 
> 
0
Confused1 (124)
10/19/2004 6:29:02 PM
Reply:

Similar Artilces:

How to find the string with (utf-8) and no sign but all the same results in MS S
How to find the string with (utf-8) and no sign but all the same results in MS SQL 2005 Example: I want to search string "nhà đẹp" or "nha dep" all the result in "nhà đẹp" Thanks, nhphuong -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201003/1 Use a case insensitive collation; for example Latin1_General_CI_AI wich is both Case Insensitive (CI) and Accent Insensitive (AI) : Select Case when N'nh� d?p' = N'nha dep' then 'Equal' Else 'Not Equal' End as Test1,...

I'm a SQL Server newbie...need help with dates
I'm using SQL 2005 Dates in Access stored as date. Imported in SQL Server using import wizard. Goes into table as 8/6/2009 12:00:00 AM my query: sum(case when transaction_date between '08/01/09' and '08/31/09' then hours else 0 end) ap2, --i get 200 which is correct sum(case when transaction_date between '07/01/09' and '09/30/09' then hours else 0 end) apQ1, --i get 200 which is correct sum(case when transaction_date between '07/01/09' and '07/30/10' then hours else 0 end) total --I get 0 why am i getting 0? thank...

Invalid Dates in Headers cause problems with Outlook 2003
Dear All: Is anyone having problems with invalid dates in headers with Outlook 2003? I recently started receiving e-mail (most of it is spam) with an invalid date in the header. When that happens, *all* Outlook tasks cease working and they are listed as Not responding in the Task Manager. To resolve this issue I log on to my mail server, which is using Horde. I then delete and purge all e-mail which has an invalid date. I then restart Outlook and it works until it receives another e-mail with an invalid date. Thank you. :-) Sincerely, Jeff V. Pulver ...

Generic row reference in formula
I am using a formula in Excel to sum the values in a row: =SUM(B2:F2) Is there a way to use a generic row reference in a formula? For example, I don't know for sure at run time if the data and formula is going to end up in the second row. What I want is something like this =SUM(B#:F#) where the # sign would indicate the current row containing the formula. Thank you very much. Rick Quatro rickquatro@gmail.com One way: =SUM(INDIRECT("B"&ROW()):INDIRECT("F"&ROW())) -- HTH, RD --------------------------------------------------------------------------- P...

How Can I Prevent Data Entry In Same Category on the Same Date?
Hello... I hope that someone can help me. I have a worksheet for which I randomly enter scores for students in several categories throughout the day. Column Headings: Date StudentName Category1 Category2 Category3, etc. I would like for Excel to display a warning message if I attempt to enter a score in the SAME category on the SAME day. Does anyone know how I may do something like this? Thanks!!! Jessi Jessi, Select the Category1 cells, C2:C10 in this example. The dates are in A2:A10. The following presumes C2 is the active (white) cell of your selection. In Data - Valida...

VB- If first cell with formula is blank, all cells in column returns blank.
Hi all, I am using ADO to connect to an excel sheet and display the data in vb form. In excel there is a column named "TAT" which has a formula t add two othe cell values to it. If any of the two cells is blank the the TAT col remains blank. If the very first cell in the TAT column has some value then VB showa all cell values in that column. But if the first cell is empty then al cells in tat column are shown blank, even if there are values in othe cells. What's the solution for this? Thank -- Message posted from http://www.ExcelForum.com Hi Of course you can try with some...

Check date does not G/L posting date
Is there a control in the A/P module to prevent the check date from being different than the General Ledger date? Use batch level posting dates. -- Charles Allen, MVP "Jack Cieniewicz" wrote: > Is there a control in the A/P module to prevent the check date from being > different than the General Ledger date? No because there are times you need the check date to be different -- ie manual checks being entered after the fact. "Jack Cieniewicz" <Jack Cieniewicz@discussions.microsoft.com> wrote in message news:8D0ECC3F-840E-46D2-AB7B-13DA652CDA0A@microso...

Internet Explorer 8 links issue
Hi All! I have a simple website I created using Windows Publisher. Up until I installed Internet Explorer 8, I've had no problems with it whatsoever. I installed IE8, and next thing you know, whenever I go to the website, the links that normally appear on the left hand side of the webpages are gone! I used IE7 on another computer to check out the website, and the links are there and I can surf to other pages with no difficulties! Is there any changes I can make to IE8 - I used pretty much the default settings... Thanks Hi, Do a web search for MS Publisher IE8 XP or try the micro...

Formula name
Hello, For the life of me, I can't figure out how to copy/paste a formula into the Name>Define>Refers To box. I've used named formulas many times but just typed the formula in. This formula is very long and complex and I just don't feel like typing it into the Refers To box. Can someone 'guide' me through this please? It's got to be simple but I'm overlooking something. TIA Sandy Sandy, copy the formula and use Ctrl v to put it in the refers to box -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post a...

How do I get charts to ignore results of a formula if it equals 0
Hi, Use NA() instead of zero. Depending on your chart type the point will not be plotted. Cheers Andy ChrisRox wrote: -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Last cell in new formula
Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

fixed asset date vs. GL date
Hello: I realize that the GL Posting date in the GL interface is the date of posting to the GL for asset additions. What date updates the fixed assets subledger, however, for fixed asset additions? Specifically, what I'm getting at has to do with importing fixed assets. In order to update fixed assets for fixed asset additions and to have the date of posting to fixed assets to be the end of the month, should I change the Great Plains user date to 01/31/06 when I conduct the import of the new assets? Or, does it even matter about the date of posting to the fixed assets subledger...

formula will not add up right
I put in =sum(E1+G23) & the total in the cell where I put the formula comes up(2 or 3) cents off? It comes up short when I put =sum(E1*G23). Not al the time but off & on. I have to add stuff on paper to check that the total is right. I should not have to take all this time to use Excel. Your question has already been answered an hour and a half ago -- Kind regards, Niek Otten "Kickstart" <Kickstart@discussions.microsoft.com> wrote in message news:DDD6AB31-657B-4F64-8709-D9E36CF96C41@microsoft.com... >I put in =sum(E1+G23) & the total in the cell where I p...

Matching Dates
Ok guys fairly complicated one here... I have a single table with 20,000 transactions within it. Each transaction has a date attached to it. I have another table which contains 3 fields, a StartDate, a FinishDate and a WeekNumber. i.e.: startdate finishdate weeknumber 01/01/2007 08/01/2007 1 What I need to be able to do is take each transactions from the transactions table and link it to this calendar table to get the weeknumber of when each transaction occured. How on earth can I produce this? Many thanks. Ash. hi, ashg657 wrote: > Ok ...

Lookup last in column formulas
Hello Excel users and experts, Is there any significant difference in these formulas that return the last value in the column. =LOOKUP(9.99999999999999+307,A:A) =LOOKUP(9.99999999999999E+307,A:A) =LOOKUP(9.999+307,A:A) =LOOKUP(9.999E+307,A:A) Why use fourteen 9's past the decimal point, either with or without the E when three 9's with or without the E works just as well? Thanks Howard Hi! Good question. Here's a bone of contention I have when I see these types of formulas. Say for example that you know for certain that the absolute largest number that could possibly ...

date and time in 2 cells to create subtraction in 3rd
I have 2 cells that are in the format line of A1: 27/06/2006 9:42:00 pm B1: 7/06/2006 9:52:05 pm Want third column C1: 00:10 I wish to subtract the two cells to get the difference in time i.e 10:00 (only interested in nearest minute) Then a average at the end of the column of the the times in this 3rd column as a result of the difference between the 2 times. How would I do this? I could do it if it was just numbers but once it is in this time/dtae format I am completely lost! Thanks MIke Hi Mike, =MOD(B1,1)-MOD(A1,1) Format as Time You can use the AVERAGE function on the ...

How do I add the file last modified date into an Excel header?
It is easy to show the date a document was last modified in MS-Word by inserting the field: savedate into the footer. A very common requirement. But there does not appear to be any way, or at least no easy way to do this in Excel. I CAN'T BELIEVE IT! They still haven't added this. Excel forces you to put in the current date. YUK! Why would I want that in my print out? I want to know when it was last modified, not when I printed it. ARG!!! From an earlier posting Hi use the following UDF: Function DocProps(prop As String) application.volatile On Error GoTo err_value ...

SQL: Summing By Date Continuously?
Two tables: tblPaymentAccrualDaily SecID PayDate PayAmount 003 01/01/2007 123.97 004 01/01/2007 4250.63 003 01/02/2007 123.43 003 01/03 2007 120.45 003 01/04/2007 119.20 003 01/05/2007 130.44 ...and so on for each and every day tblPaymentSchedule SecID PayDate 003 01/03/2007 003 02/03/2007 003 03/05/2007 003 04/03/2007 What I want to do is present, for a given SecID, the total daily accruals from tblPaymentSchedule.PayDate to the next (actually, the day after one PayDate to the next...

Help Exporting DATE values
I have a table that has 5000 dates in the "date" column. I need the date exported to a comma delimited file in this format... March 10, 2007 But the date is exported like this in the text file... 3/10/2007 0:00:00 How can I fix this? By the way, changing the format of the date field in the design view of the table doesn't seem to fix this. Help! Thanks Hi Sincity Write a query making the date field as follows dateexport: Format([date],"mmmm d"", ""yyyy") then export the query Regards Ian B "SinCity" wrote: > I have a ...

Listing 'last used' date
Hi folks. We have literally hundreds of databases and my boss wants me to try and produce a listing where we can idenatify the tables etc., and link a 'last used' date, in order to delete those that are no longer used. Has anyone any ideas about how to go about this? Mike MacKinnon wrote: > Hi folks. We have literally hundreds of databases and my boss wants > me to try and produce a listing where we can idenatify the tables > etc., and link a 'last used' date, in order to delete those that are > no longer used. > > Has anyone any ideas about how to go abou...

formulas #6
I am trying to calculate the following: =SUM(F41-25)*5%+2*1.17 f41 - 33.99 I keep on getting 2.79 when the answer is 2.86. Can someone let me in on the right format to calculate this formula. thanks Hi! Try this: =(SUM(F41-25)*5%+2)*1.17 Returns: 2.865915 Biff "Bacchus" <kdoye2112@hotmail.com> wrote in message news:LYadnTzuQrHqv9_eRVn-sg@rogers.com... >I am trying to calculate the following: > > =SUM(F41-25)*5%+2*1.17 > > f41 - 33.99 > > I keep on getting 2.79 when the answer is 2.86. Can someone let me in on > the right format to calculat...

Sent Mail Dates Replaced with word NONE
I am using Outlook 97 on a W2k machine. I have suddenly developed a problem in the Sent Items folder. Several weeks back the sent date changed from the usual date to the word NONE. The other folders seem to be OK. What and where can I check settings to look for the root cause of this? TIA, Carl -- Wishing you a VERY HAPPY NEW YEAR!! May it far exceed your expectations. ...

Remove time from date upon export to csv
I cannot remove the time from the date when exporting invoice data to a csv file. I have followed previous advice and created a query based on my tblInvoices table, in which I have formatted the InvDate field in the Design Grid as follows: Date: Format([tblInvoices.InvDate], "dd/mm/yyyy") (UK format) In the Query, this is fine - just the date, no time shown. I saved an export spec in the Advanced bit of the File/Export wizard. But in the resulting csv, there are six zeroes representing the time! However if I do a manual export by following exactly the same process bu...

julian date #3
Is there a formula or line of code that, in a date cell, would automatically convert a date entered in the 07/16/04 format into the 04188 julian format? thank you. You can't have a formula in a cell and make an entry in it - the formula is overwritten. You can use an event macro: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim nYear As Long Dim nDays As Long With Target If .Count > 1 Then Exit Sub If Not Intersect(.Cells, Ra...

internet options change and ie 8
i have ie 8 and have noticed that my internet options icon changes and im wondering what that means. the icon is a globe type image and then for some reason not of my doing (as far as i know) it changes and has a white square in front of the globe and the square has a check mark on it. its probably not a big deal but im just curious what it is doing and what it means. thank you in advance. kf. "kf" <kf@discussions.microsoft.com> wrote in message news:4F1B7252-67A7-4BEF-8BB6-983C8B8C2026@microsoft.com... > i have ie 8 and have noticed that my internet o...