# Thread: Excel formula to find same date as last year

1. ## Excel formula to find same date as last year

Hi,

I cannot find a formula anywhere that will give me the excel serial number for the same date as last year (ie 7/1/2015 I want to return serial for 7/1/2014). I will be using this number for a lookup to pull a value from the previous year's spreadsheet. Any help on this would be appreciated as I have hit the research wall. Thanks.

2. Let's assume that the date you are starting from is in cell A1.

If you are happy to ignore the effects of leap years then you can use the simple formula =A1-365

If you need to be exact, you can use =DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

Silly me ! Edited as per the posts below so as not to leave a misleading reply on this Forum.

3. ## The Following User Says Thank You to MartinM For This Useful Post:

balla506 (2014-07-22)

4. OP wanted to go BACK one year. Martin M's formula goes AHEAD one year.

5. Same principle as forward, just use -1 instead of +1:
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

Steve

6. Worked great. I actually had this at one time but was just looking at it wrong. Thanks again.