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: https://www.excelguru.ca/forum…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