Subtract one year from todays date

[Nigel]

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

 

[Mike H]

Try this with todays date in A1

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

Mike

 

[Nigel]

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

 

[ShaneDevenshire]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Mike H]

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

 

[David Biddulph]

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.