# How to calculate difference between dates in successive years

• I am keeping a Nature "Springwatch" calendar using a standard Excel spreadsheet. I have a list of the indicator species in column A.

There are then two columns for each year. As I note something e.g. 'First plum blossom', I then enter the date in the first column for that year. The Cell format is Category = Date; Type = *14/03/2001 Locale = English (UK). Excel version is 2007

What I am trying to do in the second column for each year is to show the number of days variance, as either a +/- over this year, from last year. So if the date was 10 days earlier than last year, it would be '-10', if it was 10 days later, it would be '+10'.

So far, I am unable to find a way to achive this.

NW

• Re: How to calculate difference between dates in successive years

Hello,

Not sure to understand ...

With first date in cell A2
Second date in cell B2

In cell C2 ... you can have =B2-A2-365

HTH

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Re: How to calculate difference between dates in successive years

Try:

[COLOR="#0000FF"]=TODAY()-DATE(YEAR(TODAY()),1,1)-(A2-DATE(YEAR(A2),1,1))[/COLOR]

where A2 contains date in first column

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: How to calculate difference between dates in successive years

Thanks for the interesting reply, but I couldn't get it to work.

I have attached a small portion of the spreadsheet as a demo. The columns where I am trying to calculate the figures are 'E' and 'G'.

In the demo I have manually entered the figures. So Row 5, Column G, it is the difference in days between the 26th February and the 6th March. The year is irrelevant. In this case it is +8 days (8 days later than last year)

A B C D E F G
[TABLE="width: 583"]

[tr]

[TD="width: 208"]5 Daffodil by path in flower[/TD]
[TD="class: xl63, width: 94, align: right"]07/03/2015[/TD]
[TD="class: xl63, width: 34"][/TD]
[TD="class: xl63, width: 76, align: right"]26/02/2016[/TD]
[TD="class: xl65, width: 49"] -9[/TD]
[TD="class: xl63, width: 76, align: right"]06/03/2017[/TD]
[TD="class: xl64, width: 46"]+8[/TD]

[/tr]

[/TABLE]

## Files

• Re: How to calculate difference between dates in successive years

Hello,

HTH

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Re: How to calculate difference between dates in successive years

Hi HTH

Solved! Thank you very much. That works perfectly, and so elegant as well.

NW

• Re: How to calculate difference between dates in successive years