Sum total of D:h:mm values

[awwphooey]

Hello again-
I’ve once more hit a wall. I’ve searched both Excel help and the BB, but not having much success on this one, probably because I'm not phrasing my query correctly.

You’ve helped me figure out how to calculate number of D:h:mm between dates by using:

=TEXT(F3-E3,"D:h:mm")

business objects spaced out v.1.xls
	A	B	C	D	E	F	G
3	CRF65581	1720.308	(Approve)	cward	10/19/0611:29AM	11/20/0610:41AM	31:23:11
4	CRF65581	1720.308	(Approve)	bensmith	10/19/0611:29AM	11/16/062:53PM	28:3:24
5	CRF65581	1720.308	(Assessment)	jgeslien	9/15/061:49PM	10/5/0611:14AM	19:21:24
6	CRF65581	1720.308	(Approve)	twiley	10/19/0611:29AM	11/1/068:47AM	12:21:17
7	CRF65581	1720.308	(Assessment)	twiley	10/6/066:51AM	10/18/061:27PM	12:6:35
8	CRF65581	1720.308	(Approve)	jgeslien	10/19/0611:29AM	10/27/064:16PM	8:4:46
9	CRF65581	1720.308	(Approve)	shaylock	10/19/0611:29AM	10/26/063:58PM	7:4:28
10	CRF65581	1720.308	(Approve)	hduleck	10/19/0611:29AM	10/20/063:06PM	1:3:37
11	CRF65581	1720.308	(Review)	rkellogg	9/14/063:03PM	9/15/061:49PM	0:22:46
12	CRF65581	1720.308	(Assessment)	jgeslien	10/5/061:52PM	10/5/065:07PM	0:3:14
13	CRF65581	1720.308	(Review)	hduleck	9/14/061:59PM	9/14/063:03PM	0:1:03
14	CRF65581	1720.308	(Review)	lkelly	10/5/061:51PM	10/5/061:52PM	0:0:00
15	CRF65581	1720.308	(Review)	weedle	10/6/066:50AM	10/6/066:51AM	0:00
16
17
Report 1

Now that I’ve successfully acquired these values, how can I calculate the total D:h:mm spent on column A, and then average that total time?

Thanks in advance, once again.
Phoo

 

[barry houdini]

how can I calculate the total D:h:mm spent on column A, and then average that total time?

Not sure what this means, column A is all the same, does that mean you just want to total the whole of column G and/or average that column?

You have a couple of problems with a formula like

=TEXT(F3-E3,"D:h:mm")

Firstly it returns a text value which is more difficult to sum, secondly it won't give you the correct result if any time difference is greater than 31D:23:59.

If you want to sum column G for a specific number in A try

=SUMPRODUCT(--(A3:A15="CRF65581"),F3:F15-E3:E15)

format as number to get result in days, i.e. 33.5 = 33 days 12 hrs.

If you actually want the result to look like 33 days 12:00 then use

=INT(SUMPRODUCT(--(A3:A15="CRF65581"),F3:F15-E3:E15))&" days "&TEXT(SUMPRODUCT(--(A3:A15="CRF65581"),F3:F15-E3:E15),"hh:mm")

 

[awwphooey]

Barry-

Many MANY thanks, worked like a charm.

Phoo

 

[awwphooey]

whoops

Barry-

Thanks again, I was so excited I forgot the second part of my question (apologies for not being clear enough the first time).

So now I'm at this point:

business objects spaced out v.3.xls
	A	B	C	D	E	F	G
3	CRF65581	1720.308	(Approve)	cward	10/19/0611:29AM	11/20/0610:41AM	31:23:11
4	CRF65581	1720.308	(Approve)	bensmith	10/19/0611:29AM	11/16/062:53PM	28:3:24
5	CRF65581	1720.308	(Assessment)	jgeslien	9/15/061:49PM	10/5/0611:14AM	19:21:24
6	CRF65581	1720.308	(Approve)	twiley	10/19/0611:29AM	11/1/068:47AM	12:21:17
7	CRF65581	1720.308	(Assessment)	twiley	10/6/066:51AM	10/18/061:27PM	12:6:35
8	CRF65581	1720.308	(Approve)	jgeslien	10/19/0611:29AM	10/27/064:16PM	8:4:46
9	CRF65581	1720.308	(Approve)	shaylock	10/19/0611:29AM	10/26/063:58PM	7:4:28
10	CRF65581	1720.308	(Approve)	hduleck	10/19/0611:29AM	10/20/063:06PM	1:3:37
11	CRF65581	1720.308	(Review)	rkellogg	9/14/063:03PM	9/15/061:49PM	0:22:46
12	CRF65581	1720.308	(Assessment)	jgeslien	10/5/061:52PM	10/5/065:07PM	0:3:14
13	CRF65581	1720.308	(Review)	hduleck	9/14/061:59PM	9/14/063:03PM	0:1:03
14	CRF65581	1720.308	(Review)	lkelly	10/5/061:51PM	10/5/061:52PM	0:0:00
15	CRF65581	1720.308	(Review)	weedle	10/6/066:50AM	10/6/066:51AM	0:00:00
16
17							122days19:53
Report 1

is there a way to average G3:G15?

Thanks in advance yet again.

Phoo

 

[awwphooey]

Or to state more clearly, average the total of G3:G15 (the 122 days 19:53
total)?

Phoo

 

[barry houdini]

Does this depend on what's in column A?

If you just want the average time difference for all the entries

=SUMPRODUCT(F3:F15-E3:E15)/MAX(COUNT(F3:F15),1)

or if you want to average for a specific number in column A

=SUMPRODUCT(--(A3:A15="CRF65581"),F3:F15-E3:E15)/MAX(COUNTIF(A3:A15,"CRF65581"),1)

 

[awwphooey]

Thanks

That worked well, thanks Barry.

 

[awwphooey]

Barry (et. al.)- since your last instruction, I've been continuing to do due diligence before looking to the board, but am not successful figuring out some additional manipulations I'd like to make.

You showed me how to use =INT(SUMPRODUCT(F90:F96-E90:E96))&" days "&TEXT(SUMPRODUCT(F90:F96-E90:E96),"hh:mm") to obtain the total number of days spent in column G (I removed the reference to Column A from your formula, as the CRF's play no part in the data- they are just document numbers. I'm trying to find time spent on those docs.).

business objects spaced out v.3.xls
	A	B	C	D	E	F	G
90	CRF61385	1740.015	(Approve)	mskoog	5/15/0610:18AM	6/15/061:45PM	31:3:27
91	CRF61385	1740.015	(Review)	eedolan	4/24/061:05PM	5/6/0611:04AM	11:21:58
92	CRF61385	1740.015	(Approve)	corinna	5/15/0610:18AM	5/23/063:43PM	8:5:25
93	CRF61385	1740.015	(Assessment)	amelia	5/6/0611:04AM	5/11/066:44PM	5:7:39
94	CRF61385	1740.015	(Approve)	phlexus	5/15/0610:18AM	5/17/064:40AM	1:18:21
95	CRF61385	1740.015	(Approve)	silva	5/15/0610:18AM	5/16/062:56AM	0:16:37
96	CRF61385	1740.015	(Approve)	ldwilson	5/25/067:37PM	5/25/067:40PM	0:0:02
97							59days01:32
98	CRF68461	G97052	(Approve)	mskoog	12/19/063:02PM	1/19/075:25PM	31:2:23
99	CRF68461	G97052	(Approve)	marco	12/19/063:01PM	12/22/0612:19PM	2:21:17
100	CRF68461	G97052	(Assessment)	jolgin	12/9/067:30AM	12/11/061:24PM	2:5:53
101	CRF68461	G97052	(Approve)	mitchl	12/19/063:02PM	12/20/065:36PM	1:2:34
102	CRF68461	G97052	(Review)	garyt	12/8/0610:42AM	12/9/067:30AM	0:20:48
103							38days04:57
Report 1

I've been doing this manually for each CRF document on my sheet. With over 7,000 lines of data though, this has been taking me some time. Is there a way of setting up an additional formula / macro which will add the column G data for each separate CRF in Column A?

Many thanks in advance, again.

Phoo

 

[awwphooey]

got it

Figured it out, thanks everyone for your previous assistance.

Phoo