AVERAGE function not working

Question

I Have two columns containing dates.

I used this formula to find how many days there were in between the dates:

=DATEDIF(J7,K7,"md") &""

This produces the number of days without putting the word 'day/s' after it.

I have attempted to then find the average of these results using the AVERAGE function and it returns a value of #NUM!.

Firstly, I thought that it could be as a result of using the DATEDIF function. However, I have converted the values into numbers etc, yet it produces the same result.

Here is how my sheet is set out (note the values in 'Difference Between Days' aren't correct):

| Created Date     | Closed Date      | Difference Between Days |
|------------------|------------------|-------------------------|
| 11/08/2017 20:36 | 14/09/2017 17:45 | 33                      |
| 13/06/2017 13:36 | 25/07/2017 12:20 | 45                      |
| 12/10/2017 14:53 | 10/11/2017 23:15 | 29                      |

Answer 1

@Máté Juhász was correct in suggesting that the formula didn't return as a number. I created a new column and used the VALUE function, which has allowed the AVERAGE function to return the correct result.

Answer 2

@JRigress, I would like to suggest solution for both the problems. First is about Difference of days with caption Days at the end.

Since you have not listed the date values so i'm just assuming it. For example your dates are in cell C301 14/11/2017 & D301 30/11/2017, the formula is in F301.

= DATEDIF(C301,D301,"d")&" days" (Finding difference in Days).

other is

=DATEDIF(C301,D301,"md")&" days" (Finding difference in days ignoring Month & Year)

NB: Pick the required Formula.

For first two dates you get same Result 16 Days. But if you change the date value for example C301 14/11/2017 & in D301 01/01/2018 then you can find different result.

The first formula will show 48 days and second one will show 18 days, coz it's simply counting the difference between 14th Nov to 1st Dec which is 18 (since ignoring Month & Year).

Now the second issue, I'm not sure what for you are using the Average formula but I would like to suggest you, Yes you can do it. Before apply the formula if you wish select both cells C301 & D301 then go to Cell Format and apply Number Format without decimal, remember this will not affect on previous result and finally use the Average formula.

=Average(C301,D301)

and you get the result 43077 (apply Number Format in the Result cell also).

I do hope I've solved both of your problems in case of any further query just post your comment.

@JRigress,, I think when earlier I've attempted your question you have not posted the Data sheet, just now while reviewing I got so I've solved it also.

NB: Both formula are same as previous only you have to change the Data Range.