Hi,
I'm trying to find the average date from a list of dates, all from different years, however, the actual year doesnt matter, so what i'm looking for is the average day and month.
In column A i have the following:
25/11/1974
30/11/1975
24/11/1976
17/10/1977
3/11/1978
28/11/1979
10/12/1980

Is there a formula that can find the average date? I've tried everything that i found online, but nothing work...
Thanks a lot for any help!

Hi,

To get the mean average date (I assume you want the mean) would be straightforward:
If the years are to be ignored then it will give a very different result. There are two ways your request could be interpreted which could give different results:

(1) Consider all dates to have the same year and calculate the average
(2) Consider the days only and take the average, then consider the months only and take the average.

Once the method is established, how do you want the result to be displayed? How do you want the result to be rounded? Please give an example output for the sample data you provided in post #1?

Hi Colin,
Thanks for your reply!
I can ignore the years as i'm looking for monthly trends, regardless of years.
Assuming that i ignore the years, in the above sample, i guess that the average date would be somewhere at the end of November.

I tried using the AVERAGE function in 2 different ways:
1. I isolated the yeas and was left with the day and mont only
2. I kept the years, but changed all the years to be 2000

In both times, the function returned #DIV/0!

I attached a sample printout of what i'm working on (I thought I would be able to attach a spreadsheet...)

Thanks a lot!!!

Hi,

Okay, assuming that you want to follow method one, let's use 2010 as the year base (a non-leap year). This array formula will give you a result of 19th November:
When you type it into the formula bar you need to complete the entry with CTRL+SHIFT+ENTER (not just the usual ENTER) so that Excel surrounds the formula with parentheses { }.
You then need to format the cell as a date, eg. dd mmm.

If there are blank cells in the range then the above formula would give an incorrect result so it would have to be adapted as follows (made more complicated):
This is also an array formula.


Hope that helps...

Hi Colin,
I tried it, however i'm still getting an error, and i think that it may be with the cells format. I attached a screen shot as well.
Thanks again for your help!
Roee.

From your first example, your Excel uses dates in an American format. If you type in a date in English format then Excel will not recognise it as a date (or interpret it as an incorrect date).

Try it with the original dates you posted. There's no need to change the years manually, the formula does it for you. You just need to make sure that Excel recognises the values in A1:A7 as dates.

I tried it but still no luck. I attached a screen shot of both the formula and the way i have the cell format set up.
I actually need the date not to be in American format. I need it to be dd/mm/yyyy.
Thanks!

Hi,

Your Locale is English US. Excel is expecting American dates. If you want Excel to work with UK dates then you'll need to change your region settings in your control panel. (Note that this is different to the formatting of cells which changes the way dates are presented to the user).

I have attached a working example for you.

Colin,
Its working!!! Thanks a lot!
I changed the regional settings as you suggested.
Now, my last question is if there's a way that the resualt of the function will be the day and month, but without the year showing?
In the above example, I would like it to say 19/11 and not 19/11/2010.

Thanks again!
Roee.

Hi,

The simplist thing to do is to format the cell as dd/mm so that the year is not displayed.

Hope that helps...

Hi,
I tried it earlier and it didnt work, but now its working...
You've helped me a lot, thank you very much!!!
Roee.

Hi,
I actually ran into another problem with some of the averages, and it’s due to the nature of my data (rainfall amounts and cumulative seasonal amounts based on specific thresholds). Therefore, in the below date I’m supposed to get an average date in December, however, since some of the dates are from January and February, I’m getting an average in November. I’ve also attached a spreadsheet.

Is there anyway around it? The formula that you sent me before works great for 3 out of the 5 thresholds that I’m looking into.

Thanks a lot!!!

10/12/1974
26/12/1975
28/11/1976
5/12/1977
3/12/1978
14/12/1979
27/12/1980
27/12/1981
29/12/1982
10/12/1983
13/12/1984
26/12/1985
11/11/1986
22/12/1987
6/12/1988
29/11/1989
24/12/1990
9/12/1991
8/12/1992
2/1/1994
24/11/1994
12/12/1995
15/1/1997
9/12/1997
25/12/1998
5/1/2000
13/12/2000
4/12/2001
25/12/2002
26/12/2003
26/11/2004
16/12/2005
27/12/2006
7/12/2007
10/1/2009

Hi,

I copied the data in your post into cells A1:A35.

This formula then calculates the average date in December (16th December in this case), assuming that all dates are year 2010. It ignores any dates which are not from December.
This formula is an array formula, so complete the entry with CTRL+SHIFT+ENTER.

Thank you very much!!!
It solved the problem.
Roee.