0

I'm using this formulas:

=DATEDIF(B9,S9,"d") & " Days " & TEXT(S9-B9, "h:m") & " hrs:min"   
=DATEDIF(B10,S10,"d") & " Days " & TEXT(S10-B10, "h:m") & " hrs:min"

etc..

And now i need to have a formula that calculates the average of those dates. The problem is that they are in text and excel cannot calculate average.. Would appreciate any input. Thanks

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Slider105
  • 45
  • 1
  • 2
  • 8

2 Answers2

2

Consider the following:

Excel Table

Formulas:

  • C2 = B2-A2
    (same for rows 2 through 6)

  • C7 = AVERAGE(C2:C6)

  • D2 = INT(C2) & " Days " & TEXT(C2, "h:mm") & " hrs:min"
    (same for rows 2 through 7)

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Note that TEXT(C2,"d") never gives a result greater than 31 (because it's actually showing day of month not elapsed days), so it won't be suitable for time periods of 32 days or greater - better to use INT function as per my answer – barry houdini Sep 07 '13 at 22:27
  • @barryhoudini - Excellent observation. I updated my answer. Thanks! – Matt Johnson-Pint Sep 07 '13 at 22:42
2

Your formula isn't a reliable method for calculating days and hours between two dates. Consider where B9 is 1st Jan 2013 at 22:00 and S9 is the next day 2nd Jan at 06:00 - there are only 8 hours between those two "timestamps" but your formula will give the result

1 Days 8:00 hrs:min

better to use this version

=INT(S9-B9) & " Days " & TEXT(S9-B9, "h:m") & " hrs:min"

That will give correct results in all cases

For the average you can use a formula like this

=INT(AVERAGE(S9:S18)-AVERAGE(B9:B18)) & " Days " & TEXT(AVERAGE(S9:S18)-AVERAGE(B9:B18), "h:m") & " hrs:min"

where you have data in rows 9 to 18

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Thank you both, that works! Just one more doubt, how can i display for example 26 hours instead of 1 day 2:00 hrs:min ? Thanks – Slider105 Sep 09 '13 at 17:24
  • Do you want to do that for the initial results or the average? If it's the former then just use =S9-B9 and custom format as [h]:mm - make sure you include the square brackets. For the AVERAGE use =AVERAGE(S9:S18)-AVERAGE(B9:B18) and format the same way – barry houdini Sep 09 '13 at 17:52