Average of dates excel

Question

Hello,

I have a table with a column containing multiple dates of physiological stages like: 01/05/1976, 03/04/1979. I would like to find an average date, which would be 17/04 for here. The problem is that my columns contain about 40 dates, so how can I find the average date at which the studied stage occurs?

Thank you in advance for the help

Configuration: Windows XP Internet Explorer 6.0

Sir_DEC · Accepted Answer

It works very well for me...

I create a date range in the format dd/mm/yyyy, without specifying a format.
Then I calculate the average "=AVERAGE(range)" and then I simply change the format to "Custom" -> "dd/mm" (without the quotes) by selecting it from the list.

Sir_DEC · Answer

I did this not really believing it... But the worst part is that it works:  =AVERAGE(range)  :-)

sonyblanc · Answer

I already tried but it doesn't work.

Sir_DEC · Answer

Are the cells in date or text format?

Because on my computer it works perfectly...

Otherwise, you can convert the cells to numbers, take the average, and then maybe convert them back to date.
I just tested it, and it works as well. In the column to the right of the dates, apply the values from the left by choosing the number format, then take the average in date format.

sonyblanc · Answer

I agree, you have a numerical value indeed, but it corresponds not to the month of April but rather to the month of November or something like that.
From the moment I have the year, the average does not match what I'm looking for.
If you do, how do you do it?

Sir_DEC · Answer

Well, I simply input dates in the format dd/mm/yyyy into several cells and then I calculated the average of the range, nothing more.

In numerical mode, the year is not taken into account in the average.

For example, I tested:

Date - Numeric
12/04/2006 38819
12/04/2008 39550

12/04/2007 39184.5

The last line being the average. I tested values that are consecutive as well as distant values, from two to ten values.

Edit: Make sure the date format is correct, that it is indeed dd/mm/yyyy and not mm/dd/yyyy like in English (check the language options or something like that).

sonyblanc · Answer

I find this:   12/04/2006 38819 19/04/2007 39191  39005  or 39005 gives me 15/10  where is the mistake?

sonyblanc · Answer

Certainly, but that's not what I'm looking for. In reality, my dates are in the format dd/mm/yyyy and I would like to calculate an average without considering the years, without having lengthy formatting since I have a lot of data. I know I can select the day and then the month and then combine the two, but that's very long, so if you have a faster solution.

I would like to have:

12/04/2005
18/04/2007 and an average of 15/04.

sonyblanc · Answer

It's very good thank you, I was using three columns to achieve the same result that's why I found it long. However, I can't calculate the average is that normal?

sonyblanc · Answer

I get a number that cannot be converted into a date; is that normal?

sonyblanc · Answer

It doesn't work with the custom format either. Maybe this request is not feasible....

sonyblanc · Answer

Doing the same, I find this: 23/03/1992 23/3 25/03/1993 25/3  22/9 what to do?

Sir_DEC · Answer

But it is completely normal... Why insist on wanting to find a false value?

The average between March and March of the following year is March + 6 months = September...

The average between 3 and 15 is 9...

Do you want the average over the days/months without taking into account the year? (in that case it's different)

sonyblanc · Answer

Yes, I want to calculate an average of days/months but by manipulating dates in the format dd/mm/yyyy as quickly as possible. Is that possible?

Sir_DEC · Answer

Well, I found it, but it only works for two dates, otherwise the formula gets longer...

=AVERAGE(DATE(0,MONTH(date1),DAY(date1)),DATE(0,MONTH(date2),DAY(date2)))

The formula becomes less simple, but it works, regardless of the years :)

sonyblanc · Answer

Thank you, it's progressing gradually. But I'm going to be difficult, when I have about 50 data points, what should I do?

Sir_DEC · Answer

You'll need to add a second column, estimating that a month has an average of 365/12 days... (Otherwise, it really complicates things and you'll need to add two columns)

So in the column to the right of the dates, we'll put the formula (which we'll develop across the entire column):

 =MONTH(date)*365/12+DAY(date)

Then we'll calculate the average of these data, simply.

Finally, we'll find the month and day by doing a calculation using mod 365/12.

Day = MOD(average,365/12)
Month = INT(average/365*12)

The date in a single cell: =MOD(AVERAGE(range),365/12) & "/" & INT(AVERAGE(range)/365*12)

This gives the average of the dates, not taking the year into account, in the form d/m (6 instead of 06 for example).

I think it’s good now? :-)

sonyblanc · Answer

YES, I think the first formula is good: =MONTH(date)*365/12+DAY(date) and the average works well. However, what do the following formulas correspond to? Thanks again.

Sir_DEC · Answer

The average gives us the average in number of days (counted from 1 on January 1st to 365 on December 31st), so we then need to separate the month and the day by dividing by an average number of days (365/12), thus obtaining the integer part (the month) and the remainder (the day, by multiplying it).

However, I have some errors; it seems that the day is not always an integer :)

Solution => Take the integer of the mod.

Final formula: =INT(MOD(AVERAGE(range),365/12)) & "/" & INT(AVERAGE(range)/365*12)

MOD = Remainder of a division
INT = Integer of a number
& = Symbol for concatenating (putting together) text, here a number followed by "/" and then another number.

sonyblanc · Answer

Alright, thank you! But using the date format dd/mm also works, it might even be faster!

sonyblanc · Answer

There is a small issue; when I apply the formula, I observe an error regarding the month, there is a one-month shift. What should I do now?  04/04/1977 04/05/1900 02/04/1978 02/05/1900 10/04/1979 10/05/1900 11/04/1980 11/05/1900 07/04/1981 07/05/1900 05/04/1982 05/05/1900

calculMan · Answer

I entered all the dates with the same year and I display dd/mm in cell format and I add a column on the left with the year.  15/03/2010 formatted -> 15/03  16/03/2010 formatted -> 16/03  17/03/2010 formatted -> 17/03  Average(B1:B3) => 16/03/2010 formatted -> 16/03  Column A -> year 2008  2009  2010

Average of dates excel

22 réponses

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress

Inuyasha in french

Ccleaner starts at boot.