Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2010
    Posts
    30

    Unanswered: Finding the average date

    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. #2
    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. #3
    Join Date
    Mar 2010
    Posts
    30
    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!!!
    Attached Files Attached Files

  4. #4
    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. #5
    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.
    Thanks again for your help!
    Roee.
    Attached Thumbnails Attached Thumbnails Dates.JPG  

  6. #6
    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. #7
    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!
    Attached Thumbnails Attached Thumbnails Dates.JPG  

  8. #8
    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.
    Attached Files Attached Files
    Last edited by Colin Legg; 11-11-10 at 19:02.

  9. #9
    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. #10
    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. #11
    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. #12
    Join Date
    Mar 2010
    Posts
    30
    Hi,
    I actually ran into another problem with some of the averages, and its due to the nature of my data (rainfall amounts and cumulative seasonal amounts based on specific thresholds). Therefore, in the below date Im supposed to get an average date in December, however, since some of the dates are from January and February, Im getting an average in November. Ive 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 Im 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
    Attached Files Attached Files

  13. #13
    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. #14
    Join Date
    Mar 2010
    Posts
    30
    Thank you very much!!!
    It solved the problem.
    Roee.

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