Suming Up Time over the course of days

  • Thread starter Thread starter wutzke
  • Start date Start date
W

wutzke

I have a sheet the contains event durations on specific days. Some
days have more than one event. I would like to add up all the event
times for each day on a second sheet and (line) chart the results
there.


The list get longer with each event.

Tuesday Nov.2 2010 10:16 am 00:37:22
Tuesday Nov.2 2010 08:28 am 00:15:48
Monday Nov.1 2010 08:30 am 01:03:07
Sunday Oct.31 2010 06:36 pm 00:09:20
Sunday Oct.31 2010 06:17 pm 00:03:26
Sunday Oct.31 2010 05:27 pm 00:07:42
Sunday Oct.31 2010 11:15 am 00:27:19
Sunday Oct.31 2010 08:56 am 00:45:26
Saturday Oct.30 2010 12:44 pm 00:24:47
Saturday Oct.30 2010 08:44 am 00:21:18
Friday Oct.29 2010 08:26 am 01:04:21
Thursday Oct.28 2010 08:34 am 00:59:17
Wednesday Oct.27 2010 03:11 pm 00:07:32
Wednesday Oct.27 2010 12:44 pm 00:36:42
Wednesday Oct.27 2010 08:23 am 00:18:16
Tuesday Oct.26 2010 03:10 pm 00:06:59
Tuesday Oct.26 2010 08:31 am 00:45:01
Monday Oct.25 2010 06:58 pm 00:45:58




I also have a Google Spreadsheet link
https://spreadsheets.google.com/ccc...0RxOWwwTlNTbDRWTldwQ0E&hl=en&authkey=CP6j2dgN
 
I have a sheet the contains event durations on specific days. Some
days have more than one event. I would like to add up all the event
times for each day on a second sheet and (line) chart the results
there. [....]
I also have a Google Spreadsheet linkhttps://spreadsheets.google.com/ccc?key=0AuRYdA1rSEcRdDd5dkg2a0RxOWww...
Click to expand...

I do not see any way to distinguish two events on the same day. For
example, does the data for Oct 31 2010 represent 1, 2 or 3 events?

I suggest that you add an "event" column wherein you distinguish
events by a unique identifier. You would probably put that in column
A and move everything to the right.

Then suppose you list each event identifier in Sheet2 column A. Then
in Sheet2 column B, you might put the formula starting in B2 and
copying down:

=SUMIF(Sheet1!A:A,A2,Sheet1!D:D)

formatted as Custom [h]:mm:ss.

Caveat: In Excel, time is represented as a decimal fraction of days;
and computer binary arithmetic with non-integers often produces
artifacts -- infinitesimal "errors". For example,
IF(10.1-10=0.1,TRUE) returns FALSE(!) because the result of 10.1-10 is
infinitesimally different from the representation of 0.1, which itself
is not exact.

To avoid surprising results, it would be prudent to write:

=ROUND(SUMIF(Sheet1!A:A,A2,Sheet1!D:D)*86400,0)/86400

formatted as Custom [h]:mm:ss. That ensures that comparisons will
equivalent constants or other sums will match.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
 
Back
Top