calculate average of dates

  • [xpost][/xpost]

    also posted at:…dates-across-the-year-end

    I am keeping Phenology records using an excel spreadsheet.

    I have columns for successive northern hemisphere winters, 12018/2019, 2019/2020, 2020/21 etc Each cell is formatted as Date: Type *14/03/2001

    I have a row for each species and in the date column I have four dates for one species: 27/12/2017: 06/01/2019: 14/12/2019: 20/11/2020

    What I would like to do is find an average date, consisting of a day and month, but NOT a year, for these flowers appearing.

    I have tried an array in the formula field {=AVERAGE(DATE(,MONTH(F14:I14),DAY(F14:I14)))} But this produces a date of 16/09/1900!

    My guess would be that the average date would be 21st December. Is there a different formula I can use to get this average figure.

    Thanks for reading

  • Thank you Pecoflyer. Sorry forthe late reply, I've only just seen you answer. Your suggestions seems to have worked.

  • Disappointed not to have received a response at the other forum months ago, This file's a bit different but a similar formula in cell L6 gives a much more realistic result:


    which may have to be array-entered (with Ctrl+Shift+Enter instead of plain Enter) depending on your version of Excel.

    Format d mmm to make it clear.

Participate now!

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