Formula's and dates

[Lewis Brunton]

Hi,

Does anyone know of a way to subtract a year from a date
without using the number of days?

eg. I would like to show the result of 01/09/1999 -1 year

At the moment I can only do it using "01/09/1999"-365
days, which is a pain when I come accross a leap year.

The formula I am using so far is:

=IF(A10="01/09/2000",(SUM(A10-366)),IF(A10="01/09/1996",
(SUM(A10-366)),IF(A10="01/09/1992",(SUM(A10-366)),(SUM(A10-
365)))))

As you can see I am using an IF statement to take account
of the leap years. But as the cell from which I am
calculating the date also uses the same formula, it isn't
recognised as a 'Proper' date, and the leap year part of
the expression is never executed.

Help

Cheers

Lewis Brunton

 

[Andy B]

Lewis

Try this:
=DATEVALUE(DAY(E15)&"/"&MONTH(E15)&"/"&YEAR(E15)-1)
There's probably a nicer way, but that works for me!!

Andy

 

[Arvi Laanemets]

Hi

=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))
,where original date is in A1


Arvi Laanemets

 

[Daniel.M]

Hi Lewis,

For a valid date in A10:
=DATE(YEAR(A10)-1,MONTH(A10),DAY(A10))

Regards,

Daniel M.

 

[WC]

=EDATE("01/09/2000", -12) or =EDATE(A10, -12)

- this function adds/substracts months from a date