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.


    Thanks for reading.


    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]

  • 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


    Glad you fix your problem ... :smile:


    Thanks ... for your thanks ...

    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:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!