using two cells 1 with total time n 1 with date how can i put the.

[Peter Gonzalez]

I'm trying to put the amount of time spent in to a seperate sheet (Cell # 2
column E ) from a sheet that has the dates in one cell (column A) and the
time spent in another (column K) and the reason for the time spent in column
B. On the seperate sheet I have to put the amount of time spent in column E
and match with the date I have in column A on the seperate sheet to populate
the chart that I'm making. The only thing that I have been able to do is
create a formula to show the amount of time spent and for what reason but i
can't match it to the date because the dates can come in mulitiples of the
same date.

Please help!!! It's for my job

 

[Gary''s Student]

Just an example. Say in column A A1 thru A20 we have:

1/5/2010
1/8/2010
1/7/2010
1/5/2010
1/7/2010
1/5/2010
1/9/2010
1/8/2010
1/1/2010
1/3/2010
1/6/2010
1/5/2010
1/9/2010
1/10/2010
1/5/2010
1/3/2010
1/6/2010
1/7/2010
1/2/2010
1/9/2010

and in column K we have:

4:54
3:28
3:48
0:22
0:03
0:31
3:31
0:53
9:03
5:27
9:38
0:25
0:06
4:54
6:09
0:42
4:30
2:09
0:37
3:06


There are multiple value of the different dates. In the other sheet in
column A we list the possible dates:

1/1/2010
1/2/2010
1/3/2010
1/4/2010
1/5/2010
1/6/2010
1/7/2010
1/8/2010
1/9/2010
1/10/2010

and in B1 in the other sheet we put:

=SUMPRODUCT((Sheet1!A$1:A$20=A1)*(Sheet1!K$1:K$20)) and copy down

we format this column B as Time and we see:

1/1/2010 9:03:56
1/2/2010 0:37:39
1/3/2010 6:10:00
1/4/2010 0:00:00
1/5/2010 12:24:03
1/6/2010 14:09:12
1/7/2010 6:00:43
1/8/2010 4:22:04
1/9/2010 6:44:21
1/10/2010 4:54:18

 

[Peter Gonzalez]

thanks but that didn't work for me let me show you what I got

Sheet 1 Example

Date Dept.Name1 Dept.Name2 Dept.Name3 Dept.Name4
1/18/2010 1:23 0:45 1:53 1:22
1/19/2010 1:45 2:15 5:15 0:55
1/20/2010
1/21/2010


Sheet 2 Example Same Row

Date
1/18/2010 Dept.Name2 Time Stopped Time Start Total Time Lost
(0:45)
1/18/2010 Dept.NAme1 Time Stopped Time Start Total Time Lost
(1:23)
1/19/2010 Dept.Name2 Time Stopped Time Start Total Time Lost (2:15)


the formula that I'm looking for is one that i can match the dept. name with
the date at the same time entering the Total Time Lost in its respective cell
so i don't have to go back and forth from sheet to sheet or creating the
=total time lost formula after every entry.