#### 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.

```
 0
Utf
1/20/2010 5:59:01 PM
excel.worksheet.functions 4936 articles. 2 followers.

2 Replies
697 Views

Similar Articles

[PageSpeed] 2

```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.
>
```
 0
Utf
1/20/2010 6:27:01 PM
```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.
> >
```
 0
Utf
1/20/2010 11:49:01 PM

Similar Artilces:

UI Data Validation using XML Schema
Has anybody done UI data validation usinf XML Schemas. Any pointers will help. Thanks! ...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

1.2 danish -> 3.0 english
Is this possible ? Jack ...

how to use the type member in System.Web.Services.Description
hi all, I want to parse the wsdl file . It is ease to parse the parts of wsdl except that the part of types. Because I don't know how to use the System.Web.Services.Description member types . I have searched the MSDN to find answer , but , it tell me very less. So I come here for help. For example, there is a types is defined as follows: - <types> - <s:schema elementFormDefault="qualified" targetNamespace="http://www.webservicex.net"> - <s:element name="GetGeoIP"> - <s:complexType> - <s:sequence> <s:element minOccurs="0...

Can anyone send me the Microsoft.Crm.Platform.Proxy.dll and Microsoft.Crm.Platform.Types.dll files ?
Hi, I'm stucked without those files, and I don't have the instalation disk. Can anyone send me those files ? Thanks in advance, Eddie. You can download the program from Microsoft. http://www.microsoft.com/office/onenote/prodinfo/trial.mspx#EEAA Sorry-wrong group Eddie, email me and i will send them over -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Eddie" <Eddie@discussions.microsoft.com> wrote in message news:epJ2yF3NFHA.3076@tk2msftngp13.phx.gbl... > Hi, > > I'm stucked without those files...

trouble with copying a cell to other cells.
Info adds +1 (so 3oo goes to 301) and in the same function, 2 stays a 2 when copied to another cell. How do I mark what changes and what doesn't? I found the info posted on a different post. by BenjieLop for Copying a formula 9/20/2004 I want to copy a formula from one cell to several others, it is a basic formula =c5*d12 when i copy and paste the formula changes..( =c6*d13 and so on...) I want the second part d12 to change but i want to anchor the first part somehow so the c5 is constant in every formula c5*d13 c5*d14 etc.. The formula =\$C\$5*D12 will do the trick ...

after saving to cd many times. now says read only?
i have saved many times to my cd. i overwrite it almost daily (CD-RW) all of a sudden it now say's the file is read only. although i can still save to my hard drive? nsturre wrote: > i have saved many times to my cd. i overwrite it almost daily (CD-RW) all of > a sudden it now say's the file is read only. although i can still save to my > hard drive? Unless you have formatted your CDRW for packet writing, then yes, it will be Read only. CDROM = CD Read Only Memory! -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk Just ...

Paste Several Values into one Cell
Hi all. I have data in several cells (I2:GJ2) on one row. I want to copy all of this data into one cell as it appears now (in a particular sequence). Is this possible? Thx in advance. Not sure if this is what you want but to concatenate data from several cells into 1 cell, use formula =Concatenate(I2," ",H2," ",G2) just add more cell references with commas and " " to give a space between each cell value for all the cells you want. "sgarrett" <anonymous@discussions.microsoft.com> wrote in message news:110F5183-D1CE-4C5A-A7F8-90D01E500FBE@micr...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

Dear All, I just imported about 2000 contacts in my Outlook 2000 Address Book. Able to see them in the address book and create groups etc. But when i compose a new message and i try to enter some e-mail addresses in TO and then why do outlook do not give me suggestion to auto-complete them? I tried most of the settings. It wont work. What may be the problem? -Sameer Outlook 2000 has no such feature. Autocompletion didn't start until Outlook 2002. Versions of Outlook earlier than Outlook 2002 have a feature called auto-resolution. If you enter part of a name or a nickname then tab out o...

Can't restor backup file, "Please insert the next backup disk.....
I have money 2005 backup file (.mbf) and my original file has gotten corrupted somehow and can't repair it (computer hung and had to be restarted). Whenever I try to restor the backup file, I get the following message, Window Title: Money Multiple Disk Backup Message: Please insert the next backup disk. (The disk containing file "".) I was backing up on a different connected single harddrive and there is only one file. The file is not in multiple pieces and on different drives. When I click OK, it doesn't do anything and cancel just stops the process. I even down...

How can I shade area under a norm dist graph?
I have graphed a normal distribution curve. =Normdist(x,4,4,false) for x=-8 to +18 Now I would like to shade the areas where 8.5<x<12 and x<-3. That is, the left tail and a portion of the right tail. How do I do that? TimH - For one method, see http://www.mikemiddleton.com/ShadedNormalDensity.xls - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "TimH" <TimH@discussions.microsoft.com> wrote in message news:31235839-5F38-48E1-98EF-69A0BB22C634@microsoft.com... >I have graphed a normal distribution curve. =No...

portfolio value over time
I run Money 2004 and i can't do some things i think are really basic - main two (related issues) that bother me are: 1) is there any way to view the value of my entire investment portfolio over time (e.g. as a chart showing basically just price * volume of shares i own for each investment, combined to show all investments together)? 2) is there any way to view price history for all my holdings as an indexed value (0% at the start growing up or down based on % change in price) as opposed to absolute value? Some shares are \$20 each some are \$200 each - doesn't make sense to sho...

Can't access comcast newsgroups in outlook express 6 or Outlook 2003
Hi, I had the comcast news server working in both aps, but trying to make it work with outlook 2003 as the default program to access the newsgroup reader, by removing the newsgroup account in outlook express 6, I now can't get it to connect in either ap. duh... It's completely gone from Outlook 2003, ie, where before, under the go button there was a link to news, now there is not. Outlook express 6 still has the newsgroup reader, that's how I'm sending this, but when i try to go to the comcast news server it repeatedly asks for my username and password. I deleted the a...

sum values from range of cells if cells correcponding have the sam
hiya, i have a small project and i've spent too much time on this already - can't figure it out myself. What i need to do is to create a formula which will find the same values in column A and will sum the corresponding to them values from columns B:E. this is a sort of result i want to get: A B C D E F RESULT: 1 A1 1 5 2 3 A1 31 2 B5 2 6 3 3 A2 19 3 C1 3 7 4 3 B1 22 4 A1 4 8 5 3 B5 37 5 B5 5 9 6 3 C1 33 6 C2 6 1 6 3 C2 16 7 A2 7 2 7 3 8 B1 8 3 8 3 9 C1 9 4 0 3 I would appreciate any ideas how to do this. Thanks and regards, Assume in G1 down are the unique item...

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

can't add transfers to my budget
I have 3 accounts and I want to have 2 budget plans I want to have one "main" budget that has all my expenses, then another budget for my girlfriend and I's joint expenses. We have a joint account. I wanted to add the transfer from my checking into our joint account in my "main" budget. I have a group named "Transfers out of budget accounts" but I can't add anything to it or edit it. How can I accomplish this? I say one post here: http://umpmfaq.info/faq.txt that sounded hopeful, but when I tried to follow the instructions, nothing happened. Is t...

I am looking for a cell formula that would recover the spreadsheet name and display it in a cell - more or less as "=CELL("row";A1) function is able to return the line number of cell A1 or as "=CELL("filename";C18)" will return the full path of the Excel workbook housing cell C18... Thanks a lot - Alain79 See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "Alain79" <desaivresanospam@free.fr> wrote in message news:ddsce3\$94f\$1@s1.news.oleane.net... > I am looking for a cell formula that would recover the spre...

how to get the system setup time.
I want to get the system setup time. but this code didn't work! CreateFile always return -1, and the error code is 0x00000005!(Access is denied.) TCHAR path[MAX_PATH]; if (GetWindowsDirectory(path, MAX_PATH)==0) break; HANDLE h = CreateFile(path, GENERIC_READ, FILE_SHARE_READ|FILE_SHARE_WRITE, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL); if (h==INVALID_HANDLE_VALUE) break; // always return Access is denied.! FILETIME ft1, ft2, ft3; if (!GetFileTime(h, &ft1, &ft2, &ft3)) break; thank you ! Buried in the MSDN documentation for the CreateFile function, there...

Two Screens???
We are running MS Excel 97 with MS XP OS on a computer with a Matox drive where we have two screens. Excel used to open on the left hand screen but now opens onto the right hand screen. I'm unable to resize the excel application by selecting the border. How can i get it to re-open on the left hand screen as a default? regards Wayne Wayne wrote: > We are running MS Excel 97 with MS XP OS on a computer > with a Matox drive where we have two screens. > > Excel used to open on the left hand screen but now opens > onto the right hand screen. > > I'm unable...

Can you have a Territory without a manager ?
Can you have a Territory without a manager ? Is it required ? Yes you can have a Territory without a manager. Just a little tip, all required fields are indicated in red, bold letters and all business recommended (ie suggested you fill this information in) fields are indicated in blue, bold letters. Veronica. Program Manager Microsoft Business Solutions CRM "newbie" wrote: > Can you have a Territory without a manager ? > > Is it required ? > ...

error : can't find object or library
I got this error : can't find object or library whenever i try to enter some information into a cell. can someone advise how to get raid of it? thanks Hi, Try this it may work: Most likely, the application has lost the reference to an object or type library resulting in the above error. The problem may be resolved as follows: Open the database or application. Open a module in Design view or press ALT+F11 to switch to the Visual Basic Editor. On the Tools menu, click References. Clear the check box for the type library or object library marked as "Missing:" An alter...

How can a value be converted to words
Is there a fromula if I have a total i.e. 123 to be converted in words i.e. One hundrd and twenty three automatically. http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 or http://www.xldynamic.com/source/xld.xlFAQ0004.html tj "Azar" wrote: > Is there a fromula if I have a total i.e. 123 to be converted in words i.e. > One hundrd and twenty three automatically. ...

Proper use of categories in invoicing?
I am a freelance web developer. I use Money to print and track my invoicing. Being a freelancer, I often have a wide variety of clients: there is a continual influx of new clients, as well as a stable of regular clients. The regular clients often bring their own clients with them, and in this case, my role is only to develop the site. As a result, I will often send out 10 or 20 invoices to one of these regular clients over the course of a year, covering work that I may have done on 20 or 30 sites. As it is now, I can sort for invoices to the REGULAR CLIENTS, but I cannot search/sort for i...

Can we make a document frame window title bar disapper?
Hi, friends, I registered a document template in InitInstance(). It may not be an MDI child. When I created a new document, I tried to make the frame title bar disappear by setting: cs.style = WS_DLGFRAME; in function PreCreateWindow(CREATESTRUCT& cs). Unfortunately, the title bar was still there, although no more caption. I also tried setting: cs.style &= ~WS_CAPTION; and some other combinations, no luck. Could anyone tell me how to do it? Or, can we do it? Thanks. You need to remove WS_CAPTION style. You can do it in Precreatewindow and for mainframe do it after it ha...