How to calculate the years

[mar_t]

I have two columns in Excel, the 1st column is "Date Purchased" (A1) and my 2nd column is "Estimated Year in Service" (A2). On A3 would be the result. see example below.

1-Jan-2012 + 5 = 1-Jan-2017

pls help.

thank you

[teylyn]

Hello,

if A1 has a real date, then you can use this formula to add the number of years defined in A2 with this formula in A3

=EDATE(A1,A2*12)

or

=DATE(YEAR(A1+A2),MONTH(A1),DAY(A1))

cheers,

[bebo021999]

=EDATE(A1,A2*12)

Make sure your excel 2003 have Analysis Toolpak installed already.

=DATE(YEAR(A1+A2),MONTH(A1),DAY(A1))

should be:

Please Login or Register  to view this content.

[teylyn]

bebo, thanks for the corrections. You're right, of course. My mouse is playing up and driving me bonkers, irritating me to the point of neglecting detail. Thanks for picking it up.

[mar_t]

Thanks guys..it worked!...i made a couple of changes on my table.
=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))

Now, I have two more concerns.

1. If A1 and B1 are blank C1 displays "0-Jan-1900". I want to have a blank result in C1..is this possible?
2. In cell C1, I want to filter only the YEAR (regardless of month/day). On the example below, If i want to filter only 2014 it will display only 3 records.

11-Dec-2011 2 11-Dec-2013
10-May-2011 3 10-May-2014
13-Jan-2012 3 13-Jan-2015
13-Jan-2012 2 13-Jan-2014
13-Jan-2012 2 13-Jan-2014

Please help, thank you.

[teylyn]

Hello,

you can wrap the formula in an IF statement, for example execute it only if the number of values in A1 and B1 is 2:

=If(count(A1:B1)=2,DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)),"")

If you want to filter by year, you have two possibilities:

Either format the whole column C with custom format

yyyy

so only the year shows. Or, if you still want to see the full date in column C, create a helper column in column D with the formula

=IF(ISNUMBER(C1),YEAR(C1),"")

copy down, and then filter on the values in that column.

In later versions of Excel, the filters are more sophisticated and you can select the year right there, even though the column shows the whole date, but with 2003 you need to use one of the workarounds above.

cheers,