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"

    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

    Quote from Billyrj

    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

    Quote from Billyrj

    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!

Register Yourself Login