Excel 2007 : Averages with dates and times

[Cdo]

I am using a formula to calculate the time between date in and time in, and date out and time out for my company.

For example, the "date in" is column A, "time in" is column B, "date out" is column C, and "time out" is column D. If you are familiar, the formula is to find the difference (time) between is as follows:

=C2-A2-(B2>D2)&" days "&TEXT(1+D2-B2,"hh:mm")

I want to find the average of all the times collectively, but when I try to use the function in Excel, it does not work. I figure because it is in days and time, not just hours or minutes.

Please send suggestions on how to do this! I would really appreciate it!

[DonkeyOte]

You would generally find life easier if you stored the difference in an adjacent cell, eg:

E2: =SUM(C2:D2)-SUM(A2:B2)
copied down

The result is simply the AVERAGE of Column E
result cell format as [hh]:mm etc to show average in terms of Time

[Cdo]

You would generally find life easier if you stored the difference in an adjacent cell, eg:

E2: =SUM(C2:D2)-SUM(A2:B2)
copied down

The result is simply the AVERAGE of Column E
result cell format as [hh]:mm etc to show average in terms of Time

Thank you for responding! I am somewhat confused still but I am hoping it is a communication error...The equation I posted is what I already have in a separate column (E). I have a lot of data and basically for that column, I am trying to find the average...


So I have hundreds of cells in that column that look like...4days02:29...I want to be able to average all of those in a separate cell...Thank you

[daddylonglegs]

It's difficult to average the results you have in column E because they are text strings not numbers. Donkeyote suggests using another column (F?) with the formula

=SUM(C2:D2)-SUM(A2:B2)

That uses the base data to get the difference as a number (a decimal number of days). You can then average that column using a simple average formula like

=AVERAGE(F2:F100)

....or if you want to show the result in the same format as before then you can use

=INT(AVERAGE(F2:F100))&" days "&TEXT(AVERAGE(F2:F100),"hh:mm")

...there are ways you can average your text strings in column E dierctly.....but it can get complex, e.g. like this

=TEXT(SUMPRODUCT((0&LEFT(E2:E100,FIND(" ",E2:E100&" ")-1))+RIGHT(0&E2:E100,5))/COUNTA(E2:E100),"d"" days ""hh:mm")

only works if the average is less than 32 days.....

[Cdo]

Instead of averaging it...is there a way to translate into only minutes or hours...?

So like I had before..G=E3-C3-(D3>F3)&" days "&TEXT(1+F3-D3,"hh:mm")...

And in Column H, I want to translate my answer in Column G to only hours or minutes....

[daddylonglegs]

You can do a straight subtraction, i.e. in H3

=F3+E3-D3-C3

and format as [h]:mm

[Cdo]

How do you format? Is it better do it in the equation or by changing the setting in Excel?

[shg]

Change the format via the UI, so that the result remains a number that can be used in downstream calculation.

[Cdo]

Change the format via the UI, so that the result remains a number that can be used in downstream calculation.

What is the UI? How do I do that? Thank you for all the help...I am not an Excel Pro at all!~

[shg]

The user interface -- Format > Cells