CODEWITHSUNDEEP
exceldateif-statementaveragedatediff
Slider105
Slider105

Reputation: 45

Calculate average from dates in text

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

Upvotes: 0

Views: 297

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241450

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)

Upvotes: 2

barry houdini
barry houdini

Reputation: 46331

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

Upvotes: 2

Related Questions