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

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

    Code
    1. =ROUND(AVERAGE(IF(NOT(ISBLANK($C6:$K6)),$C6:$K6-DATE(LEFT($C$3:$K$3,4),1,0))),0)

    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.