CODEWITHSUNDEEP
exceldate
monkey intern
monkey intern

Reputation: 726

How to make an average of hour a job finishes at?

I have the following type of data:

**15**/02/2017 0:58
**12**/02/2017 0:12

And I want to make an average of which hour does the job finishes at. This is a huge file, 200k rows, and I don't care about the day, only when does the job usually finish. The problem is that if an hour is below 0:00 and another is above, AND they are from non consecutive days, the operation does not work. I believe this is because the date is similar to how it is done in unix, with miliseconds past a certain threshold date.

What I've tried: I thought of two approaches:

1) I convert every row to either 01/01/1990 or 02/01/1990 since I do not care about the day, only the hour. Then I try and calculate the average.

2) I convert the date to miliseconds, and substract the base date of excel to every single cell, and from there I make a normal average.

The problem for the first is that I do not know how to access specific info from a date in excel, like, hours and minutes, so I find trouble doing this since I do not know the syntax (so far I have something like =IF(B:B<= TIME(0:00); "01/01/1900"; "02/02/1900"), but it's still lacking info and does not work).

The problem for the second is that I feel is more prone to errors on my part and overly complicated to simply calculate an average of hours, too many operations. But I know little of excel so I am not sure if a longer path might be more correct in this case.

Upvotes: 1

Views: 48

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Use this array formula to get the average of just the times:

=AVERAGE(MOD(A1:A2,1))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Then format the cell as hh:mm:ss

enter image description here

To get the average hour completed if it then use this:

=TIME(ROUNDDOWN(IF(AVERAGE(IF(HOUR(A1:A2)<=12,HOUR(A1:A2),HOUR(A1:A2)-24))<0,24,0)+AVERAGE(IF(HOUR(A1:A2)<=12,HOUR(A1:A2),HOUR(A1:A2)-24)),0),0,0)

enter image description here

Upvotes: 1

Zerk
Zerk

Reputation: 1593

You could perhaps just use string manipulation to extract the hour number into a helper column and run an average against the column:

Assuming the format you've posted is a strict format all dates are provided in, and held in column A:

=VALUE(MID(A1,SEARCH(" ",A1,1)+1,SEARCH(":",A1,1)-SEARCH(" ",A1,1)-1))

Then simply average the helper column.

Upvotes: 0

Related Questions