Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using two cells 1 with total time n 1 with date how can i put the.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using two cells 1 with total time n 1 with date how can i put the.
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 "Peter Gonzalez" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using two cells 1 with total time n 1 with date how can i put
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. "Gary''s Student" wrote: 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 "Peter Gonzalez" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
totaling time in 2 cells w/ total of hours worked in 3rd | Excel Discussion (Misc queries) | |||
Split date from date time cells dd/mm/yyyy hh:mm. New cell dd/mm/y | Excel Discussion (Misc queries) | |||
How do I get total hours from 2 date/time cells? | Excel Discussion (Misc queries) | |||
Grand total time between two total cells | Excel Discussion (Misc queries) | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) |