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

What value you have to add to A1 to get it to increment the date will depend
on what time of day you have in A1. You say you have =NOW() in A1, but you
have apparently formatted the cell to show the date, and not the time too.
Changer the cell formatting if you want to see the time as well as the date.

If you don't want A1 to include the time, replace =NOW() by =TODAY(), or by
=MOD(NOW(),1)
In either case, however, adding 1.25 days to 1/5/2008 will only give
2/5/2008 06:00, not 3/5/2008.
--
David Biddulph

"Mike H" wrote in message
...
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