# Average text time

1. ## Average text time

A B C D
1 1/2/07 12:01 PM 1/10/07 5:51 PM 8:5:50 197
2 1/5/07 12:01 PM 1/10/07 8:51 AM 4:20:50 116
3 1/2/07 12:01 PM 1/10/07 11:51 PM 8:11:50 203

5 #DIV/0 #DIV/0
6 #DIV/0 #DIV/0

C1=TEXT(B1-A1, "d:h:mm")
C2=TEXT(B2-A2, "d:d:mm")
C3=TEXT(B3-A3, "d:h:mm")

C5=AVERAGE(C1:C3)
C6=AVERAGE(D1:D3)

How can I get an average in "d:h:mm" and in "h" when using a text cell calculation, without getting the #DIV/0 error?

Do I need to convert the text cell calculation result into a numerical value?

Thx

2. Naming your range "list" the formula would be :
=AVERAGE(IF(NOT(ISERROR(list)),list,FALSE))
Enter as array with Ctrl+Shift+Enter

3. Simplest way is to not use TEXT function, e.g. in C1 just use

=B1-A1 and use cell formatting to display as d:h:mm

You can then get an average in the usual way

Note d:h:mm format (even within TEXT function) won't show days greater than 31

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

#### 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