1. Registered User
Join Date
Mar 2010
Posts
30

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!

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

To get the mean average date (I assume you want the mean) would be straightforward:
Code:
`=AVERAGE(A1:A7)`
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.
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?
Last edited by Colin Legg; 11-11-10 at 14:46.

3. Registered User
Join Date
Mar 2010
Posts
30
Hi Colin,
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!!!

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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:
Code:
`=AVERAGE(DATE(2010,MONTH(A1:A7),DAY(A1:A7)))`
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):
Code:
`=AVERAGE(IF(ISNUMBER(A1:A7),DATE(2010,MONTH(A1:A7),DAY(A1:A7))))`
This is also an array formula.

Hope that helps...
Last edited by Colin Legg; 11-11-10 at 17:20.

5. Registered User
Join Date
Mar 2010
Posts
30
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.
Roee.

6. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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.

7. Registered User
Join Date
Mar 2010
Posts
30
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!

8. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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.
Last edited by Colin Legg; 11-11-10 at 19:02.

9. Registered User
Join Date
Mar 2010
Posts
30
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.

10. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

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

Hope that helps...

11. Registered User
Join Date
Mar 2010
Posts
30
Hi,
I tried it earlier and it didnt work, but now its working...
You've helped me a lot, thank you very much!!!
Roee.

12. Registered User
Join Date
Mar 2010
Posts
30
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

13. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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.
Code:
`=AVERAGE(IF(MONTH(A1:A35)=12,DATE(2010,12,DAY(A1:A35))))`
This formula is an array formula, so complete the entry with CTRL+SHIFT+ENTER.

14. Registered User
Join Date
Mar 2010
Posts
30
Thank you very much!!!
It solved the problem.
Roee.

15. Registered User
Join Date
Jan 2015
Posts
1

Average start date

Hi,

I'm trying to find the average start date from the below list of start dates (employees). I've tried the above formulae but no dice. The year is relevant.

25.11.2006
08.04.2014
29.08.2011
27.08.2013
28.07.2014
03.11.2010
03.12.2014
11.02.2009
10.10.2011
28.12.2011
22.08.2012
05.02.2010
27.05.2011
09.06.2011
07.08.2014
14.12.2008
27.11.2013
15.01.2013
11.01.2010
26.11.2013
01.08.2011
28.10.2014
25.06.2008
29.08.2011
15.11.2006
07.02.2014
01.07.2011
22.08.2012
02.06.2014
31.03.2014
05.06.2014
03.04.2014
09.06.2011
10.10.2006
16.07.2014
30.07.2014
24.06.2006

Any ideas?

Cheers

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•