calculate average of dates

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    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

  • Post by jerry_maguire ().

    This post was deleted by rory ().