work out how many trucks came in between the hours of 9am and 3pm

hi there l am after a formula which will calculate how many trucks came in 
from the hours of 9 am and 3 pm on any day. the main information is in sheet 
1 and l want the value to return to sheet 2.  How do l do this? and what 
formula is best to use.
A:A           time in      time out      transport coompany  amount of 
pallets       
                 
1-Apr	4:00	5:26	LEOCATAS	WJT982		BRIAN	B 	68	TATURA	656223	T15.02	68	1
1-Apr	4:06	5:45	LEOCATAS	UMV991		DAVE	B	68	TATURA	656224	T13.02	68	1
1-Apr	5:08	6:00	HUNTERS	QZW243		RODNEY	B	68	TATURA	656228	T17.17	68	1
1-Apr	5:50	6:25	LEOCATAS	TIP994		BILL	S	22	TATURA	656233	T21.57	22	1
1-Apr	5:50	6:25	LEOCATAS	TIP993		PETER	S	22	TATURA	656232	T21.57	22	1
1-Apr	5:50	6:40	LEOCATAS	UZJ995		ANTHONY	B	51	TATURA	656225-656231	T1.63T33.34	51	1
1-Apr	7:20	8:50	LEOCATAS	WMK995		JIM	B	44	TATURA	656214	T32.56	44	1
1-Apr	16:45	16:25	MITCHELLS	VV34HO		BRENDON	B	35	TATURA	656229	T33.62	35	1
1-Apr	19:30	20:21	GATTUSO	XIZ235		BRETT	B	38	TATURA	656635	T33.78	38	1
1-Apr	19:31	20:21	GATTUSO	GTP010		STEWEY	B	38	TATURA	656634	T35.66	38	1
1-Apr	19:31	22:05	GATTUSO	GTP017		PETER	B	68	TATURA	656641	T13.06	68	1
1-Apr	19:45	21:07	GATTUSO	GTP013		GARY	B	68	TATURA	656642	T13.21	68	1
1-Apr	23:55	0:50	UNILEVER	VV34HOI		BRENDAN	B	38	TATURA	656938	T34.69	38	1
6-Apr	4:15	5:31	LEOCATAS	UZJ995		DAVE	B 	34	TATURA	656636	T31.86	34	1
6-Apr	5:00	6:00	LEOCATAS	WMK995		JIM	B	44	TATURA	656633	T32.56	44	1
6-Apr	5:05	6:00	HUNTERS	QZW243		RODNEY	B	68	TATURA	656990	T14.87	68	1
6-Apr	5:40	6:50	LEOCATAS	UMV991		RONNY	B	68	TATURA	656643	T15.21	68	1
6-Apr	5:45	6:50	LEOCATAS	UHT987		ADRIAN	B	34	TATURA	656936	T32.56	34	1
6-Apr	5:50	6:45	LEOCATAS	TIP993		PETER	S	31	TATURA	656645-656937	T.87T22.52	31	1
6-Apr	5:55	7:10	LEOCATAS	TIP994		BILL	S	22	TATURA	656637	T18.08	22	1
7-Apr	19:15	20:00	GATTUSO	XIX235		PETER	B	68	TATURA	657636	T14.99	68	1
7-Apr	19:25	20:40	GATTUSO	GLS904		DAVID	B	68	TATURA	657635	T15.12	68	1
7-Apr	19:27	20:05	GATTUSO	GTP012		DAVE	B	35	TATURA	657624	T32.95	35	1
7-Apr	19:55	20:36	GATTUSO	GTP015		MATT	S	25	TATURA	657626	T23.11	25	1
7-Apr	20:20	23:45	SPD	QWF171		JOHN	B	34	TATURA	657867	33.08	34	1
8-Apr	4:00	6:03	LEOCATAS	UZJ995		DAVE	B 	34	TATURA	657631	T32.35	34	1
8-Apr	4:00	5:40	LEOCATAS	UHT987		ADRIAN	B	32	TATURA	657860	T28.50	32	1
8-Apr	5:10	5:59	HUNTERS	QXW243		RODNEY	B	34	TATURA	657627	T31.51	34	1
8-Apr	5:34	6:14	LEOCATAS	TIP993		TIM	S	23	TATURA	657814	T20.72	23	1
8-Apr	5:35	6:15	LEOCATAS	TIP994		BILL	S	38	TATURA	657866	T8.22	38	1
8-Apr	6:25	7:13	KEATINGS	WOV491		MARK	B	34	TATURA	657819		34	1
8-Apr	14:20	15:21	HUNTERS	WIL926		STEVE	B	35	TATURA	658043		35	1
8-Apr	17:25	18:10	GATTUSO	GTP011		PETER	B	35	TATURA	658101	T33.28	35	1

-- 
chris
0
Utf
4/12/2010 9:19:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
683 Views

Similar Articles

[PageSpeed] 16

In sheet2, you enter something like:

=countif(sheet1!b:b,">="&time(9,0,0))-countif(sheet1!b:b,">"&time(15,0,0))

Assumes the time you want to check is in column B.

Regards,
Fred

"hotlh" <hotlh@discussions.microsoft.com> wrote in message 
news:37AEF640-AACB-456D-B0A9-4C6ECAB435AC@microsoft.com...
> hi there l am after a formula which will calculate how many trucks came in
> from the hours of 9 am and 3 pm on any day. the main information is in 
> sheet
> 1 and l want the value to return to sheet 2.  How do l do this? and what
> formula is best to use.
> A:A           time in      time out      transport coompany  amount of
> pallets
>
> 1-Apr 4:00 5:26 LEOCATAS WJT982 BRIAN B 68 TATURA 656223 T15.02 68 1
> 1-Apr 4:06 5:45 LEOCATAS UMV991 DAVE B 68 TATURA 656224 T13.02 68 1
> 1-Apr 5:08 6:00 HUNTERS QZW243 RODNEY B 68 TATURA 656228 T17.17 68 1
> 1-Apr 5:50 6:25 LEOCATAS TIP994 BILL S 22 TATURA 656233 T21.57 22 1
> 1-Apr 5:50 6:25 LEOCATAS TIP993 PETER S 22 TATURA 656232 T21.57 22 1
> 1-Apr 5:50 6:40 LEOCATAS UZJ995 ANTHONY B 51 TATURA 656225-656231 
> T1.63T33.34 51 1
> 1-Apr 7:20 8:50 LEOCATAS WMK995 JIM B 44 TATURA 656214 T32.56 44 1
> 1-Apr 16:45 16:25 MITCHELLS VV34HO BRENDON B 35 TATURA 656229 T33.62 35 1
> 1-Apr 19:30 20:21 GATTUSO XIZ235 BRETT B 38 TATURA 656635 T33.78 38 1
> 1-Apr 19:31 20:21 GATTUSO GTP010 STEWEY B 38 TATURA 656634 T35.66 38 1
> 1-Apr 19:31 22:05 GATTUSO GTP017 PETER B 68 TATURA 656641 T13.06 68 1
> 1-Apr 19:45 21:07 GATTUSO GTP013 GARY B 68 TATURA 656642 T13.21 68 1
> 1-Apr 23:55 0:50 UNILEVER VV34HOI BRENDAN B 38 TATURA 656938 T34.69 38 1
> 6-Apr 4:15 5:31 LEOCATAS UZJ995 DAVE B 34 TATURA 656636 T31.86 34 1
> 6-Apr 5:00 6:00 LEOCATAS WMK995 JIM B 44 TATURA 656633 T32.56 44 1
> 6-Apr 5:05 6:00 HUNTERS QZW243 RODNEY B 68 TATURA 656990 T14.87 68 1
> 6-Apr 5:40 6:50 LEOCATAS UMV991 RONNY B 68 TATURA 656643 T15.21 68 1
> 6-Apr 5:45 6:50 LEOCATAS UHT987 ADRIAN B 34 TATURA 656936 T32.56 34 1
> 6-Apr 5:50 6:45 LEOCATAS TIP993 PETER S 31 TATURA 656645-656937 T.87T22.52 
> 31 1
> 6-Apr 5:55 7:10 LEOCATAS TIP994 BILL S 22 TATURA 656637 T18.08 22 1
> 7-Apr 19:15 20:00 GATTUSO XIX235 PETER B 68 TATURA 657636 T14.99 68 1
> 7-Apr 19:25 20:40 GATTUSO GLS904 DAVID B 68 TATURA 657635 T15.12 68 1
> 7-Apr 19:27 20:05 GATTUSO GTP012 DAVE B 35 TATURA 657624 T32.95 35 1
> 7-Apr 19:55 20:36 GATTUSO GTP015 MATT S 25 TATURA 657626 T23.11 25 1
> 7-Apr 20:20 23:45 SPD QWF171 JOHN B 34 TATURA 657867 33.08 34 1
> 8-Apr 4:00 6:03 LEOCATAS UZJ995 DAVE B 34 TATURA 657631 T32.35 34 1
> 8-Apr 4:00 5:40 LEOCATAS UHT987 ADRIAN B 32 TATURA 657860 T28.50 32 1
> 8-Apr 5:10 5:59 HUNTERS QXW243 RODNEY B 34 TATURA 657627 T31.51 34 1
> 8-Apr 5:34 6:14 LEOCATAS TIP993 TIM S 23 TATURA 657814 T20.72 23 1
> 8-Apr 5:35 6:15 LEOCATAS TIP994 BILL S 38 TATURA 657866 T8.22 38 1
> 8-Apr 6:25 7:13 KEATINGS WOV491 MARK B 34 TATURA 657819 34 1
> 8-Apr 14:20 15:21 HUNTERS WIL926 STEVE B 35 TATURA 658043 35 1
> 8-Apr 17:25 18:10 GATTUSO GTP011 PETER B 35 TATURA 658101 T33.28 35 1
>
> -- 
> chris 

0
Fred
4/12/2010 11:25:08 PM
Reply:

Similar Artilces:

E2K change password not working
We are using Exchange 2000 on a Windows 2000 server. I need to get the Change password option working. I'm trying to follow Q297121. My question is with the Certificate. Let me back up. Our staff members can access their email accounts thru OWA. This is internal and external. From what article 297121 states, in order for the Change password option to work, it will require a SSL cert. I can load Certificate services on this server so that it will generate a certificate (I think...I've never done this before!!!). What I'm concerned with is, it sounds like it will work...

Report stopped working
I have a report I've been working on for quite some time now and for some reason it has stopped working this morning. I did edit one of the source tables yesterday but this shouldn't have effected its operation. In fact even attempting to copy the report or open the VBA behind it will cause the database to crash. I tried creating a blank DB and importing everything into it, but it crashes at the aforementioned report. If I simply try to run the report it will flash open for less than a second before shutting itself down. What's going on, and how do I fix it? H The big ...

Outlook 2007 has stopped working (when connecting to exchange)
Hi, running outlook Enterprise 2007 on Vista Home Premium (X64), this error message (Outlook 2007 has stopped working) happened before install of Vista SP2 and after (I was hoping the update might fix, but alas). I can get into outlook, everything is fine till I type in my password to connect to exchange (hosted through intermedia.net). I have the same configuration on 2 other machines (to the same account). One machine running XP with Outlook 2003, another machine runnning Vista Ultimate (X64) and Outlook 2007 enterprise, and I have no problems. I get intermittent messages about ha...

Will nmemonic keys work inside a CTabCtrl control ?
I've added a VK_TAB handler to allow users to tab into my CTabCtrl control. They can tab around on the controls. However, the nmemonic keys don't work at all. The nmemonic keys that reside outside the CTabCtrl work fine, like the &Ok/&Apply/&Cancel/&Help common buttons. Are there any known work arounds for this ? Tim On Feb 29, 9:48=A0am, Tubs <tda...@gmail.com> wrote: > I've added a VK_TAB handler to allow users to tab into my CTabCtrl > control. They can tab around on the controls. However, thenmemonic > keys don't work at all. Thenmemonickey...

Compact folder function doesn't appear to work
On two different machines, one with XP and OL 2002 and the other W2K with OL 2000. When I perform the compact function it flashes the little message Compacting... for about 2 seconds. When I check the file size it does not change, even though I've deleted or archived at least 1-2 Mb worth of messages (in several cases, quite a bit more. I typically use shift-delete, so it is not as if the stuff is sitting in my deleted items folder. I use F5 to refresh my view of the directory that it is in, I see the file date/time update, but no change in the file size, none. I even exit OL, still no ch...

Notes 2003 Connector - cant recieve mail
(sorry for cross post, i posted in outlook.interop by accident) Hi all, I have what seems to be a strange situation. I am using the Outlook 2003 notes connector. I had the connector working successfully for a few months. My account was then migrated to a different domino server. Once this happened my inbound mail stopped working. My outbound mail still works, appointment requests come thru, but inbound email is kaput. Outlook thinks it is synching with notes and says it completes sucessfully, but no mail shows up (however its there in my notes client). I am at a loss for how to debug t...

SetProps doesn't work for the last folder from the hierarchy structure ?
Hi, I encountered some problems when I tried to programaticaly change the PR_DEF_POST_DISPLAYNAME and PR_DEF_POST_MSGCLASS properties for the contacts folder structure: these properties were properly changed for all folders, subfolders except the last one from the hierarchy. Ex: Contacts -> SubFolderContacts -> SubSubFolderContacts The properties were properly changed for Contacts and SubFolderContacts but it weren't changed for SubSubFolderContacts. Note: in the application I didn't receive any error message when I tried to change the properties. Regards, Istvan ...

My Save As menu is not working
Save As under the file menu in Excel is "grayed out" so I cannot select it. How can I fix this problem Hi, right-click the menubar select Customise from list >> Toolbars click Standard click Reset Button ok - Mark >-----Original Message----- >Save As under the file menu in Excel is "grayed out" so I cannot select it. >How can I fix this problem >. > ...

Formulas not working #2
I have a workbook in which none of the formulas are working. Even something as simple as "=j67" which should just show the value from J67 does not work. Other workbooks are all fine, it's just this one that has the problem. I have even had multiple workbooks opened at once and can switch back and forth, where it will work in all others, but then it doesn't in this one. This workbook does have some code attached to it, but this doesn't seem to be the problem as I even disable macros when opening it and it still doesn't work. When you type the formula (any kind) into...

System manager not working properly
Last week I was goingt o move a mailbox from one mailbox store to another and when I right click on a user the following error message comes up: The server is not operational. Facility: Win32 ID no: 8007203a Exchange System Manager Seems that something is broken but I can't figure out what it is. Any ideas as to what is wrong and is there a fix floating around out there somewhere? Thanks in advance. -- Glenn MCP A+ Network Admin MBBC Have you looked here... http://support.microsoft.com/default.aspx?kbid=325322 Here is another article that points to DNS issues... http://www.mcse.m...

How to create a bar chart of hours worked by day?
Excel 2003. I have a spreadsheet containing the columns: Date, Time In, Time Out. E.g., 5/11/05 08:30 17:30 5/12/05 10:30 18:00 Is it possible to create a chart from this data with the following characteristics? If so, how? 1. Dates down the left side. 2. Across the bottom, a time-of-day scale, beginning at 0800 and ending at 2200, with labels at one-hour intervals. I.e., 0800 0900 1000 1100 ... 2200 3. For each date, a horizontal bar which begins at the Time In value for that date and ends at the Time Out value for that date. Optionally: 4. Ability to enter multiple, non-con...

if product key dont work what do you do
i called microsolft they dont know so and solations people any body have this problem if no im going to 4 get a bout it its and say its a wast of money Jhon, Where did you purchase it from? That would be where I would start because it sounds as the key provided has been tampered with. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "jhon cenas hot lol" <jhon cenas hot lol@discussions.microsoft.com> wrote in message ...

Working of formula in Macro.
Hi, I have written a macro which process around 50000 rows in a spreadsheet and i have used VLOOKUP function in it. When i use this function normally(I mean manually) it takes around 2 mins to pop-up the values in spread sheet and macro process it in seconds. I just wanted to know does that macro waits untill the data is populated or not. Regards Heera Chavan hi, Updating your worksheet 'on the fly' while running a macro causes everything to slow down so it's often beneficial to stop it and also stop calculation and re-start at the end like this sub nn() Ap...

Help!!!!. Code is not working correctly
Could anyone help me out with these codes. Something is wrong but I can't figure it out. This particular line of code is suppose to have 3 OpenArgs for the form "Invoice by Dates" but only the first two gets included. Me.cmbReviewer.Value is not included DoCmd.OpenForm "Invoice by Dates", acNormal, acWindowNormal, OpenArgs:=Me.txtStartDate.Value & "," & Me.txtEndDate.Value & "," & "," & Me.cmbReviewer.Value This snippet of code is suppose to tell me where the first comma, the 2nd comma and the 3rd comma are in the...

too many printer sessions!
I have a Windows 2003 Terminal server. When clients connect remotely they can see everyone elses locally created printers. Is there a way to have the use only see their own printer sessions? Thank you in advance ...

Too Many Recepients Problem
Hi, Is their a way out from Too many recepients problem when I try to send an email to a distribution list of 100 contacts? Abhay "Abhay Sobti" wrote in message news:uG4dT26bIHA.5128@TK2MSFTNGP05.phx.gbl... > Is their a way out from Too many recepients problem when I try to > send an email to a distribution list of 100 contacts? Send the same e-mail to less than the maximum number of recipients permitted by your e-mail provider's anti-spam quota. Repeat sending your same e-mail to less than maximum recipient count until all recipients have been included. If ...

Data Connection wizard quits working
I'm trying to connect to a new data source but the wizard stops working at the point I'm supposed to select the data source. I have been able to make the connection on several other computers, but not this one. This is what I did step-by-step: Data, Import External Data, Import Data. Clicked the "New source" button on the "Select Data Source" dialog box. Selected "ODBC DSN" and clicked "Next" button. Selected the appropriate data source and clicked "Next" button. At this point my computer does nothing-just sits there. I am able to...

DPM backups stops working after Dec Windows patches
Hi, I have an issue with my DPM 2007 running in a windows 2003 server. It was running perfect for more than a year My problem is with a cluster windows 2008 server, SP2, 64x enterprise, active-passive, running SQL server 2008 In Dec-23, 2009, I installed the following windows patches on the SQL Cluster and DPM server: Security Update for Windows Server 2008 x64 Edition (KB973565) Update for Windows Server 2008 x64 Edition (KB973687) Cumulative Security Update for Internet Explorer 8 for Windows Server 2008 x64 Edition (KB976325) Security Update for Windows Server 2008 x64 Edition...

Help Just applied August Post SP Rollup Exchange 2000 "To" Button not working
I have just applied the August Post SP3 Rollup on two of my exchange servers. On the two that were patched I am not able to click the "To" button and open the GAL. One the two servers that were not upgraded I am still able to open the GAL using OWA. I'm able to type a name in the "to" field and it is able to resolve the name however when I try to send the message it never gets delivered and a new window opens. Any Ideas? I don't know that this has anything to do with the rollup package. The symptoms you are seeing typically occur when clients are unable to co...

Provisioning system not working
I have newly installed PS 2007 and projects are not publishing because it says they cannot be provisioned. I don't know if there is a way around this issue or if there is a way to provision the workspace. The Provisioning Workspace button on the from "server settings" just errors out with some bla... about WSS not working. Well thats not helpful cause there are no errors anywhere to indicate how to resolve. Any clues would be helpful. William: If you think that the error messages you are seeing aren't helpful, imagine how less helpful they are when you ...

MRMS Work Order
Hi, I tried to create a work order using a voucher item. It will create the work order, but will not ask for the Serial number. But when we try to invoice (full payment) this work order containing Voucher item, its showing some vague error like " Invalid quantity". I am able to invoice a voucher item directly. But am not able to Invoice a workorder containing Voucher item. Please help me out of this prob Thanks in advance -- Anujith ...

with-param not working in XSLT
Hi, I'm using msxml4 sp1 to transform some xml. My template looks like this: <xsl:call-template name="rowCounter"> <xsl:with-param name="max_cols" select="2"/> <xsl:with-param name="N" select="1"/> </xsl:call-template> <xsl:template name="rowCounter"> <xsl:param name="max_cols"/> <xsl:param name="N"/> <!-- If there are any entries in any section with a position number of $N then we produce a new table row --> param:<xsl:value-of select=&...

Calculate error when one-to-many replationship is added
Table 1 ItemId (primary key) ItemName ItemDescription Table 2 Bil ItemID Quantity Received Table 3 No ItemID Quantity In I Create a query for each of the tables. which sums Quantity Received, Quantity In, Quantity Out I create another query which will add the Items table (table 1) and the other 3 queries summing up the items, joining the 3 queries to the Items table by ItemID using Join 2 (show all the fields in TblItem) for each join. Add eg ItemID, Item Name from the Items table then, from the other 3 queries, SumOfQuantity Received, SumOfQuantity In, SumOfQuantity Out the Query. In anoth...

coordination between work thread and ui thread
let's say A is a UI thread, B is a work thread there is a tree control in A, B is responsible for populating the tree contents. B fires event to let A know the conent change. then A refresh the tree control. but there is cases like this: during the treecontrol insert it's items, it recieve the event from B, then it should populates it's items again. i think their might be problem here, right? is there any good way to avoid this? > let's say A is a UI thread, B is a work thread > > there is a tree control in A, B is responsible for populating the tree > contents....

AutoComplete Only Working On Some Contacts?
Hi, I've just upgraded from office xp, to office 2003. Before I did I exported all my outlook data to a pst file, and then imported it into outlook 2003. However I've noticed now, that when I write emails, the AutoComplete on names doesn't work with everyone. i.e. 5 names, Bruce, Geoff, Maria, Mark & Marcus. If I type 'B' nothing happens, if I type 'G' autocomplete suggests 'Geoff', but then if I type 'M' it only suggests Mark & Marcus, not Maria. I've checked the contacts details, all are layed out exactly the same, with: 'f...