# average time between multiple dates

• Have been working a spread sheet and have ran into a snag. I calculate the time between two dates and come up with years and months.

My problem now is I need to look at we will say 5 and figure out the average. The formula I used to calculate time between dates is below. How to I calculate the average between multiple dates on test sheet starting at T2. I was trying to create a calculation box but cannot figure a means of doing so. I am open to any ideas

Thanks

Code
``````=DATEDIF(I2,'Run Zones'!\$U\$8,"y")&" Years "
&DATEDIF(I2,'Run Zones'!\$U\$8,"YM") &" Months"``````

## Files

• Hello

Try this:

- in U4 = AVERAGE(I4:I8)

- IN U5 = DATA.DIFF(U4;'Run Zones'!\$U\$8;"y")&" Years " & DATA.DIFF(U4;'Run Zones'!\$U\$8;"YM") &" Months"

Bye bye

Mario

• In a cell, M11 say, enter

Code
``='Run Zones'!U8-AVERAGE(I2:I10)``

Now in your result cell enter

Code
``=DATEDIF(0,M11,"y")& " years "&DATEDIF(0,M11,"ym")&" months"``

## Files

• Thanks works great. But I have a couple of questions.

1) why does M11 say 1/25/1925? I cant make sense out of that.

last question is probably more on the line with how to properly align a spreadsheet. I will have to change range ='Run Zones'!U8-AVERAGE(I5:I10)

I5:I10 range will change quite a bit for data calculations. Approx 10K rows. I am unsure of a good dashboard setup. Any ideas will be helpful.

again thanks and works perfect

• why does M11 say 1/25/1925? I cant make sense out of that.

That is because the cell is formatted as date. How Excel works with dates is explained e.g. here https://www.excelcampus.com/fu…/how-dates-work-in-excel/

Just remember that the underlying value is a number which Excel will show (format) however you wish ( e.g. a date) to the outside world without affecting the value.

So for Excel 1/24 is one hour and 1 is a day

• last question is probably more on the line with how to properly align a spreadsheet. I will have to change range ='Run Zones'!U8-AVERAGE(I5:I10)

I5:I10 range will change quite a bit for data calculations. Approx 10K rows. I am unsure of a good dashboard setup. Any ideas will be helpful.

You can replace the range reference using an Excel Table which will adapt automatically to the length of the range. ( there are other more convoluted methods)

See https://www.contextures.com/xlexceltable01.html on how-to

• Thanks this works and have been struggling on properly using tables getting better but never learned early on. My biggest issue is i will need to feed the range or manually change to provide best results. Thanks and really appreciate the assist.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!