# Adding Years To A Date (taking Into Account Leap Years)

Aug 28, 2007I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.

View 9 RepliesI have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.

View 9 RepliesADVERTISEMENT

It looks like a pretty silly problem but it's driving me mad. The yearfrac function takes a two dates and a basis. Now assume basis = 1 ( Act / Act ) and two cases:

1) 06-Jan-06 -> 06-Jan-08 ( 730 days ) = 1.998175182 =>> divisor = 730/1.998175182 = 365.33333

2) 06-Jan-05 -> 06-Jan-07 ( 730 days ) = 2 =>> divisor = 730/2 = 365

In case 1) 2008 is a leap year ( but the "leap" has yet to come! ). Can anyone explain me the logic behind? I suppose the extra day is divided by 3 but where is the "Actuality" of the divisor?

I have some cells which must be in the format 15/06/2007 15:25

I then need to add either days, months or years onto it.

Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00

There are two columns in an excel sheet, one is date of birth and other is date of reteirment

looks somewhat like this:

D_O_B D_O_R

5-Mar-53 31-Mar-11

30-Jun-57 30-Jun-15

20-Jun-51 30-Jun-09

2-Feb-55 28-Feb-13

2-Jul-51

13-Oct-55

1-Sep-51

7-Jul-54

14-Mar-53

3-Aug-50 3 0-Sep-13

Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column.

D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)

In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -

d1 e1

Restricted 5

etc

Example of data

ie.

a1 b1 c1

Restricted 1/06/2012 1/06/2017

What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.

Right now, in a column I would like to display an number (length of employment) based on the hire date.

In one cell the employee's hire date is entered. In a column of other cells the pay period ending date is displayed and in another column the length of employment is displayed.

Example:

03/17/2000

03/01/2009 8

03/15/2009 8

03/29/2009 9

04/12/2009 9

04/26/2009 9

How would I create a formula in the length of employment cells that would indicate the correct number of years based on the hire date and adjusts when the pay period date passes this hire date?

Basically I need to add a column to my source data so I can use it as a filter on my Pivot Table in a different workbook. - something just as simple as TRUE/FALSE if the date is YTD for all years would be ideal.

Have attached an example if that makes it any clearer! nice simple formula would be ideal as sheet is around 600,000 rows long and growing!

I need to auto generate and populate some cells with todays date but three years from now.

View 3 Replies View RelatedI would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.

Such as someone reaching 40 injury free years in June of this year I can let them know.

Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.

View 10 Replies View RelatedI've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.

I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.

I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.

Right now, I have, on Source col L:

Code:

=MID(C2,1,9)

and in source M:

Code:

=IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2)

This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M

Then, in Log col R, I have an array formula:

Code:

=VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)

This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)

Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.

I am working on a retirement calculation sheet. For retirement calculations the employees need to figure out their highest yearly income for three out of their last ten years. The three years can be based on any consecutive twelve months but each of the three years must be the same months, (example, Jan thru Dec, or July thru June). The three years cannot overlap.

Here is what I have so far. Column A, paydate. Column B, amount. Column C, year to date amount. Column D, amount for past 12 months.

I would like to list the three highest years in the first 3 rows of Column E.

Any help is greatly appreciated.

Pay Date Gross Year to Date Gross

Past 12 Months Highest 3 Years 08/10/06 3,594.58 3,594.58 3,594.58

08/24/06 3,332.79 6,927.37 6,927.37

09/07/06 2,595.59 9,522.96 9,522.96

09/21/06 2,457.36 11,980.32 11,980.32

10/05/06 2,457.36 14,437.68 14,437.68

10/19/06 2,457.36 16,895.04 16,895.04

11/02/06 6,212.51 23,107.55 23,107.55

11/16/06 3,206.09 26,313.64 26,313.64

11/30/06 4,104.56 30,418.20 30,418.20........

Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.

original formula from post 3261273

{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}

New formulas needed or revised from the above:

Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)

Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"

Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.

Event Date

ID

CREATE

Suspend

STOPPED

Unsuspend

Elapsed Days (Create to Suspend but NO Stopped)

Elapsed Days (Suspend Only, No stopped or Unsuspend)

[Code] .........

how to change years in a row after i put in a year in K i have add the file

View 2 Replies View RelatedI need to calculate YTD sums using the data that spans three years. I am using

SUM(B2:INDEX(B2:AC2,MATCH($B$10,$B$1:$AC$1,0))). This works fine if all of my data is contained in one year. However, when my data spans multiple years it does not. For example, if I have the following and today is April 2007 I want to calculate Jan-April 2007.

Labor Type09/0610/0611/0612/0601/0702/0703/0704/0705/0706/0707/0708/0709/0710/0711/0712/0701/08

Bus External2404526907569511,2018808059439237781,1216191,0639378001,156

Bus Internal1,4412,7942,9992,5914,0093,9444,2714,3254,7593,5493,0172,4371,9262,9722,3711,7582,931

IT External1,3483,3993,8653,8586,0247,75510,11110,40911,86911,42311,29710,4878,32713,13412,96912,41917,265

IT Internal3,3537,7428,7107,88412,73112,74614,91114,66215,30413,80513,13511,8248,63511,7728,8876,80911,283

Staff Aug12138195169195408796641626340403428469675604395637

I have 2 dates..

09-feb-09

15-dec-09

Is therea way to calculate the number of years? this is equivalent to 0.27777 years.

Is there a way to get this with the datedif or another functiom?

I have list of dates:

e.g: D/M/Y

1/03/1997

22/05/2005

13/09/1945

I want a new list that just shows the year and is formatted as a number

e.g:

1997

2005

1945

Is there a way of doing this without doing it manually, I have 20,000 observations.

The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference

View 2 Replies View Relatedi am trying to make and age table to see if a player is too old by years, months and days all i can do is Years i need something like the following

1/10/2009 cut off date birthday is 8/5/1991

all i can do is the following

=YEAR(A3)-YEAR(A2)

Please refer to the attached screenshot of my working spreadsheet.

I'm attempting to concatenate lines 86 and 87, month and year together.

The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"

I have two dates, one is a start date the other is todays date, I want to subtract the start date from todays date and show the number of years. But a small twist is I only want to take the years away from each other, ignore day/month. Start 01/05/2000 todays date 01/10/2008 years = 8. Start 10/10/2000 todays date 01/10/2008 years = 7, want it to still show 8 years and ignore the start day/month.

View 2 Replies View RelatedI got a column with dates like:

07/01/11

06/30/11

12/31/11

01/01/12

02/14/12

etc.

Now i'd like to populate a combobox with only unique years [i.e. 11 and 12]

I have a spreadsheet with my students that needs to find the date a person will be 18. based on their date of birth. I have =B27+(365.25*18) where their DOB is in B27. I have had several instances where it is correct and several instances where it is a day off.

View 4 Replies View RelatedI have been trying to figure out a formula to convert the amount of months in a certain cell to years.

I have come up with:

=INT(E29/12)&" years "&MOD(E29,12) & " months"

The problem is it comes up with 2 years 3.37063951499227 months

How I change it to come up with 2.4 months, round it to the highest number instead of loads of decimal places? I have tried change cell E29 do 0 decimal places but it doesn't work.

I need to calculate a quote over 4 years but the first and last years are not full 12 months.

On Row 20 are monthly costs for each year. The start and end date is in Row 3.

I need to find a formula that can calculate ANNUAL TOTAL for the all these year in Row 22 populated automatically, not just manually times the months in each cell.

Here is a sample file.

I created a wedding list with a bunch of fields for each household: first name person 1, second name person 1, first name person 2, second name person 2, street #, street address, apartment, city, state and zip. Then I realized that I probably needed to combine fields for each line of the address so I created 3 combined fields: 'combined 1 ' that looks like this "joe smith & patsy cline," a 'combined 2,' that looks like "14 jones street, #3," and a 'combined 3,' that looks like "New York, NY 10037."

I haven't printed to labels for years and when I did some quick research via the help function on excel it said to print labels via word...which worked fine until i got to this section: " In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help. "

I have no idea what this means, but here's what I need to figure out: " how do I print labels using just 3 of 8 existing columns in my spreadsheet, i.e I just want to use combined 1, combined 2 and combined 3 for the 3 lines of the address. My first instinct was just to cut the contents of the combined fields into a new spreadsheet and just do it taht way, but I created the combined fields using a formula which relates to the other fields........

In Column B I have various dates i.e [01/02/2008]. I Need a formula that will count the number of times the year 2008 appears in column B.

View 6 Replies View RelatedI am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.

View 9 Replies View RelatedI have the following:

A2= 01/20/2000 B2= 02/05/2000

A3= 09/05/2001 B3= 10/15/2008

C2 and C3 is where the answer will be.

what formula can be used to find out how many January 31th has passed between those ranges of years?

I'm wondering how you can quickly create a row of labels holding Year 1, Year 2, Year 3, etc. I used to know how but I can't seem to remember anymore and can't find how to anywhere.

View 3 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved