# 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.

• Your result seems correct, just create a custom format like dd/mm instead of

*14/03/2001

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