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

Asked By Peter Gonzalez

20-Jan-10 12:59 PM

I am 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 am 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
cannot match it to the date because the dates can come in mulitiples of the
same date.

Please help!!! it is for my job

20-Jan-10 01:27 PM

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

--
Gary''s Student - gsnu200909

20-Jan-10 06:49 PM

thanks but that did not 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 am 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 do not have to go back and forth from sheet to sheet or creating the
=total time lost formula after every entry.