Seems like a fairly simple question but I can't find an answer anywhere,

the date in cell A1 is todays date, and I need to subtract 1 year from that
date, not 365 days because I need to take into account leap years

thanks

Try this with todays date in A1

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

Mike

"Nigel" wrote:

Seems like a fairly simple question but I can't find an answer anywhere,

the date in cell A1 is todays date, and I need to subtract 1 year from that
date, not 365 days because I need to take into account leap years

thanks

you would think excel had a function along the lines now()- 1 year

"Mike H" wrote:

Try this with todays date in A1

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

Mike

"Nigel" wrote:

Seems like a fairly simple question but I can't find an answer anywhere,

the date in cell A1 is todays date, and I need to subtract 1 year from that
date, not 365 days because I need to take into account leap years

thanks

Hi Nigel,

It does - use =EDATE(Now(),-12)

This function is part of the Analysis ToolPak which you can attach by
choosing Tools, Add-Ins.

-12 means 12 months in the past.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Nigel" wrote:

you would think excel had a function along the lines now()- 1 year

"Mike H" wrote:

Try this with todays date in A1

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

Mike

"Nigel" wrote:

Seems like a fairly simple question but I can't find an answer anywhere,

the date in cell A1 is todays date, and I need to subtract 1 year from that
date, not 365 days because I need to take into account leap years

thanks

Just to point out for the OP... the two suggested formulas, namely...

=EDATE(NOW(),-12)

and

=DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW()))

will produce different results if NOW() equates to a leap day (such as
February 29, 2008).

Rick


"ShaneDevenshire" wrote in
message ...
Hi Nigel,

It does - use =EDATE(Now(),-12)

This function is part of the Analysis ToolPak which you can attach by
choosing Tools, Add-Ins.

-12 means 12 months in the past.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Nigel" wrote:

you would think excel had a function along the lines now()- 1 year

"Mike H" wrote:

Try this with todays date in A1

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

Mike

"Nigel" wrote:

Seems like a fairly simple question but I can't find an answer
anywhere,

the date in cell A1 is todays date, and I need to subtract 1 year
from that
date, not 365 days because I need to take into account leap years

thanks

Good and interesting point but which is correct?

With A1 containing the formula =Now() evaluating as 1/5/2008
A2 containing the formula =A1+1.25 evaluating as 2/5/2008

In my view a1+1.25 days should give a date of 3/5/2008 but it doesn't and it
doesn't roll over until we add 1.28 days to Now()

I've never understod why Excel; which clearly recognises decimal days,
chooses to roll over on what appears to be an arbitary number. I may be
mistaken in making a connection between this and the leap year anomaly you
have pointed out but feel there may be one. Is there a simple explanation?

Mike


"Rick Rothstein (MVP - VB)" wrote:

Just to point out for the OP... the two suggested formulas, namely...

=EDATE(NOW(),-12)

and

=DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW()))

will produce different results if NOW() equates to a leap day (such as
February 29, 2008).

Rick


"ShaneDevenshire" wrote in
message ...
Hi Nigel,

It does - use =EDATE(Now(),-12)

This function is part of the Analysis ToolPak which you can attach by
choosing Tools, Add-Ins.

-12 means 12 months in the past.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP

"Nigel" wrote:

you would think excel had a function along the lines now()- 1 year

"Mike H" wrote:

Try this with todays date in A1

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

Mike

"Nigel" wrote:

Seems like a fairly simple question but I can't find an answer
anywhere,

the date in cell A1 is todays date, and I need to subtract 1 year
from that
date, not 365 days because I need to take into account leap years

thanks