Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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:

Average Custom Format

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.