Subtracting dates from years

A

allison.rogers

hello

I wonder if someone can help me. I need to deduct a number of years
from a date.

If you do cell - yrs ie: 24/07/2038 - 65 = 20/05/2038

but I need 24/07/2038 - 65 = 24/07/1973
 
C

CurlyDave

When I use the Function Wizard this works

=DATE(MONTH(A1), DAY(A1), YEAR(A1) -65)

But does not display on the worksheet as so. always shows an incorrect
date from 1909,

This will work as an alternative, format the cell as date

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)-65)
 
D

Dave Peterson

=date()
expects year, month, day
in that order.
When I use the Function Wizard this works

=DATE(MONTH(A1), DAY(A1), YEAR(A1) -65)

But does not display on the worksheet as so. always shows an incorrect
date from 1909,

This will work as an alternative, format the cell as date

=DATEVALUE(MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)-65)
Click to expand...
 
S

Shane Devenshire

Hi,

If cell A1 has your date and B1 the number of years

=EDATE(A1,-B1*12)

If you are using Excel 2003 or earlier, the ATP must be attached: Tools,
Add-Ins, and check Analysis ToolPak
 
Top