I am trying to calculate the average of 302 rows of data that have generated the time difference between two columns of dates.

e.g., (Column C) = 4/28/2016 10:37:00 AM (Column D) = 5/2/2016 11:56:51 AM (Column E has calculated the difference in times as: 4 days, 1 hour, 19 minutes and 51 seconds) using the formula:

=INT(D283-C283) & " days, " & HOUR(D283-C283) & " hours, " & MINUTE(D283-C283) & " minutes and " & SECOND(D283-C283) & " seconds"

Now is there a way to generate the average for column E data? If so, what is the formula please? Thanks in advance!

  • 1
    Not easily the way you have it written, use formatting instead in column E and leave it as a number then you can average it normally. – gtwebb May 12 '16 at 16:36
  • Don't use column E: go back to the original data (Dn-Cn). – AFH May 12 '16 at 16:38

For column E (E1) just use =D1-C1 then format it using a custom format as

dd "days" hh " hours" mm "minute" ss "seconds"

Then column E is still saved as a number and can be averaged normally.

This will also make sure you don't get negative minutes or hours like you could by breaking down the values and then subtracting each part.

| improve this answer | |
  • You're the best! It worked! Thank you SO MUCH gtwebb! – Kimberly Owens May 12 '16 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.