Calculate date average in excel

Is there a way to calculate date average, but just "ignoring" the year? Let me explain. I have these dates:

If I use =AVERAGE(F2:F39), the result will be 12/15/2008. That's not what I need. What I really would like is to determine the average Day and Month. There are a couple of December dates there that i could probably eliminate, because they are way off, but using the rest of the dates, I guess that the average would somewhere around, let's say, July 12 (regardless of the year) for example. I hope this makes sense. if more details are needed, please let me know. Thanks!

4 Answers

Of course, you could create a cell with a formula that uses MONTH and DAY of the date but the same year for each one. Then when you average that column you get the average of the day/month. If you want me to include the formula in my answer then reply and I'll update.

You can do this without a helper column, with the caveat that this will not work if there are blank cells in the range.

{=AVERAGE(DATE(2015,MONTH(F2:F21),DAY(F2:F21)))}

A quick note. This is an array formula (as indicated by the curly brackets in the formula). Enter the formula without the curly brackets, then press Ctrl + Shift + Enter (instead of just Enter) to confirm the formula.

Add an extra column in which you calculate day of the year:

= F1 - DATE(YEAR(A1),1,1) + 1

Then average that, round or truncate it as you wish, then convert that back to a date as demonstrated in this SU question.

Since you can use any arbitrary year there you should not have difficulty with leap years.

Since you don't want fractional answers, you'll have to use ROUND() also. But the best way is to create a Month and Day column that use MONTH() and DAY() functions and average/round the results of each respective column (formulas shown here):

 Date Month Day
41852 =MONTH(P30) =DAY(P30)
41821 =MONTH(P31) =DAY(P31)
41821 =MONTH(P32) =DAY(P32)
41852 =MONTH(P33) =DAY(P33)
34881 =MONTH(P34) =DAY(P34)
41030 =MONTH(P35) =DAY(P35)
40695 =MONTH(P36) =DAY(P36)
40725 =MONTH(P37) =DAY(P37)
40881 =MONTH(P38) =DAY(P38)
40725 =MONTH(P39) =DAY(P39)
40881 =MONTH(P40) =DAY(P40)
40725 =MONTH(P41) =DAY(P41)
40756 =MONTH(P42) =DAY(P42)
40391 =MONTH(P43) =DAY(P43)
40391 =MONTH(P44) =DAY(P44)
40391 =MONTH(P45) =DAY(P45)
34881 =MONTH(P46) =DAY(P46)
34881 =MONTH(P47) =DAY(P47)
34881 =MONTH(P48) =DAY(P48)
34516 =MONTH(P49) =DAY(P49)
=Q50&"/"&R50 =ROUND(AVERAGE(Q30:Q49),0) =ROUND(AVERAGE(R30:R49),0)

Values shown here:

Date Month Day
8/1/2014 8 1
7/1/2014 7 1
7/1/2014 7 1
8/1/2014 8 1
7/1/1995 7 1
5/1/2012 5 1
6/1/2011 6 1
7/1/2011 7 1
12/4/2011 12 4
7/1/2011 7 1
12/4/2011 12 4
7/1/2011 7 1
8/1/2011 8 1
8/1/2010 8 1
8/1/2010 8 1
8/1/2010 8 1
7/1/1995 7 1
7/1/1995 7 1
7/1/1995 7 1
7/1/1994 7 1 8/1 8 1