Formula's and dates

  • Thread starter Thread starter Lewis Brunton
  • Start date Start date
L

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
 
Lewis

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

Andy
 
Hi

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


Arvi Laanemets
 
Hi Lewis,

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

Regards,

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

- this function adds/substracts months from a date
 
Back
Top