# Average using Elapsed Time processing a number of docs

I process documents and want to get a formula showing the average time to process each document.

Here's my data:

• number of docs processed (column G)
• start date/time (H)
• end date/time (I)
• elapsed time (J)

A simple average formula in the corresponding chart is yielding a `#NUM!`.

The time format I'm using in column J is:

`````` =DATEDIF(H3,I3,"d") & " Days " &TEXT(I3-H3, "h:m" )& " hrs:min"
``````

I'd like to get the average time spent on each document in my chart.

-

I recommend simplifying your approach. Assuming your date/time fields are properly entered/formatted (i.e. Excel recognizes them as date/time values and converts them to serial values). Then for your elapsed time, simply subtract your start from your end. This will result in a serial number that can easily be calculated into an average and custom formated into a text value (e.g. dd" days, "hh" hours, "mm" minutes, ":ss" seconds").

Here's what the values may look like:

Then just use those values to calculate your averages, and apply the proper formating to the results.

-

Looks to me that your problem lies on the function you use on column J:

``````=DATEDIF(H3,I3,"d") & "Days" & TEXT(I3-H3,"h:m") & "hrs:min"
``````

I'm doing this by head, but the result, assuming `H3 = 1/1/2000` and `I3 = 2/1/2000` will be the following:

``````1 Days 0:0 hrs:min
``````

The problem might be that the AVERAGE function only supports numbers, not text (or, at least, isn't supposed to read text in a way you would think).

Try to make a simple difference using only DATEDIF, that will give you a number (in your case, the number of days, although you can change that to minutes or hours, or any unit you desire) that you can use in an AVERAGE function.

-