calculate average of dates

    [xpost][/xpost]

    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

    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:

    Code
    =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.

Participate now!

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

Register Yourself Login