+ Reply to Thread
Results 1 to 8 of 8

Average Days between a range of dates?

  1. #1
    Registered User
    Join Date
    02-08-2008
    Posts
    3

    Question Average Days between a range of dates?

    Is there a way to calculate the average days between a column of dates?

    Basically, I've got a sheet that contains a table of entries, and each entry gets a date stamp in the first column. I would like to have the sheet display the average time, in days, between entries. Is this possible?

    Thank you in advance!
    ~cp

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi,

    Start date in A1, End date in B1, then in C1

    =(B1-A1)/2 format to General
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    02-08-2008
    Posts
    3
    So there's no way to just average the difference between a single column of dates?

    ie, The start date would be the first row of the column, and the next date would be the end date for the first range, and the start of the next, then the following row would be the end date of that range, and the start of the next, ad infinitum. Does that make any sense? I don't want to have to enter the same date in two columns =\

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi,

    To get an average between two dates you need to take one from the other, if all the dates are in the same column how do you know which is the start date and which is the end date?

    Does this picture help, if you put your formula in C2

    =(B2-B1)/2 then select C1:C2 and drag down the averages fill in automatically
    Attached Images Attached Images

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,202
    So you are looking for the overall average length of each range within a single column where the final date of one range = the start date of the next?

    It should be doable with array formulas but haven't been able to figure it out.

    ChemistB

  6. #6
    Registered User
    Join Date
    02-08-2008
    Posts
    3
    Quote Originally Posted by oldchippy
    Hi,

    To get an average between two dates you need to take one from the other, if all the dates are in the same column how do you know which is the start date and which is the end date?

    Does this picture help, if you put your formula in C2

    =(B2-B1)/2 then select C1:C2 and drag down the averages fill in automatically

    Nice, that's perfect. I tried something similar earlier, but must have screwed it up. Thanks for your help though, that's more or less exactly what I was looking for. Of course it had to be millions of times simpler than I'd expected. Go figure!

    Thanks again!
    ~c

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Glad it helped - thanks for the feedback

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,677
    Sorry, perhaps I didn't understand this.....

    Did you want the average of all the differences, A2-A1, A3-A2, A4-A3 etc?
    Assuming that the dates are all in ascending order you could calculate that with the difference betwen last and first dates divided by the number of dates less 1, i.e.

    =(MAX(A:A)-MIN(A:A))/(COUNT(A:A)-1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1