1

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

enter image description here

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!

1
  • By the way, if you think your December dates are "way off" then maybe you should use the Geometric Mean (GEOMEAN) because it's very good for reducing the effect of wayward values.
    – Lefty
    May 6 '15 at 15:12
1

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.

2
  • Perfect, I didn't think about your option before. I guess my brain is getting tired. I used =DATEVALUE(TEXT(F2,"mm/dd")), and that added the same year to all the dates. That worked great, but if you have a better option please let me know. Thanks!
    – cubanGuy
    May 6 '15 at 15:10
  • I've deleted it now but it was similar to Jan Doggen's answer, I used =DATE(2001,MONTH(A1), DAY(A1)) so it created a list of dates in 2001. You may need o format the cells to get them to display as dates - including the AVERAGE (or GEOMEAN) result that you calculate.
    – Lefty
    May 6 '15 at 15:21
2

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.

1
  • Very interesting approach. I'll give a try.
    – cubanGuy
    May 7 '15 at 18:48
1

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.

0

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
1
  • 1
    This will give you the average month, and the average day of the month. It will not, however, return the average month/day of the year. May 7 '15 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.