#### Rota spreadsheet - counting query

```Hope someone can help as I'm at a total loss.

I've got a rota that shows all personnel details, with shift patterns:
9am - 3pm
8am - 4pm
10pm - 4am
etc (shift patterns subject to change and are individual)

I have a column that shows them 'clocked on' and another for 'clocked
off'.

Now for legal purposes I need to know who is 'on' at regular
intervals:

7 am
7.10 am
7.20 am
7.30 am

so thinking logically I'd say if for the 7 am count if they are
'clocked in' but not 'clocked off' then count that person but I can't
seem to find the right formula to ensure all personnel are counted.

Has anyone got any advice - it needs to be in excel for a number of
reason

--
polgar
-----------------------------------------------------------------------
polgara's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1625

```
 0
11/9/2004 1:06:14 PM
excel 39879 articles. 2 followers.

1 Replies
693 Views

Similar Articles

[PageSpeed] 53

```Have you tried this?
>>
>> ON        OFF        Check    IN/OUT
>> 07:00      13:00      08:00     =if(and(c2>=a2,c2<=b2),"IN","OUT")
>>
>> Then COUNTIF the IN/OUT column

"polgara" <polgara.1fggam@excelforum-nospam.com> wrote in message
news:polgara.1fggam@excelforum-nospam.com...
>
> Hope someone can help as I'm at a total loss.
>
> I've got a rota that shows all personnel details, with shift patterns:
> 9am - 3pm
> 8am - 4pm
> 10pm - 4am
> etc (shift patterns subject to change and are individual)
>
> I have a column that shows them 'clocked on' and another for 'clocked
> off'.
>
> Now for legal purposes I need to know who is 'on' at regular
> intervals:
>
> 7 am
> 7.10 am
> 7.20 am
> 7.30 am
>
> so thinking logically I'd say if for the 7 am count if they are
> 'clocked in' but not 'clocked off' then count that person but I can't
> seem to find the right formula to ensure all personnel are counted.
>
> Has anyone got any advice - it needs to be in excel for a number of
> reasons
>
>
> --
> polgara
> ------------------------------------------------------------------------
> polgara's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=16254
>

```
 0
11/10/2004 10:25:37 PM

Similar Artilces:

Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes reaches around 500 characters. Not huge, but larger than the Text limitation to which is what Access 2007 of course converts this. How can I force the data type to be Memo while I'm in the query so the resulting table displays all the data without any truncation. -- TIA Eric S UPRR I don't believe you can. I think you'll have to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "E...

Retrieving Count of rows returned from MS Access Query
Hi Folks, probably a pretty routine question here ... I am trying to determine the number of rows that would be returned prior to executing an Access query . for example: If the user selects more than 65,536 possible rows, I want to warn them and then either proceed with a subset, or give them the option of cancelling their query and specifying more selective criteria. It is a dynamic query, which lets the users click on multiple list boxes to select the "Where" component of the excel query, and I want to enable them to select right up to the maximum number of rows. Ultimately,...

Need help with the query.
How can I get desire results below. I cannot seem to figure this out. Any help is greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL DROP TABLE #Temp GO CREATE TABLE #Temp ( UserHistoryId INT NULL, UserId VARCHAR(6) NULL, EventDate DATETIME NULL ) GO INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664, 'User1', CONVERT(DATETIME, 0x00008eac00000000)) INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014, 'User1', CONVERT(DATETIME, 0x00009c2600ef152a)...

Update Queries after Table Field Name Changes
In Access 2007 I have a set of tables in which the name of the index field has been changed. How can I update the queries, based on these tables? In data view I get the 'Enter Parameter Value' box. Enering the new field name in various formats gets me no results. Trying to get into design view the error box 'Databaser can't represent the join expression Table1.oldname=Table2.oldname in Design View' appears. Underneath it says 'one or more fields may have been renamed'. Yes, I know. But, how can I update the query to the new field names? Any help would b...

Microsoft Query #8
I'm using Excel to query an oracle database. When I go to Microsoft Query to edit my query, I can't view my criteria or tables. I click on the view menu and am unable to select view "criteria" or "tables". Thus, all that shows up in the screen is the data in my query. This has happend to me before. Can anybody help me? I was able to answer my own question. It seems when query is complicated enough, you are unable to view the criteria, since you are now using SQL to query the data. It seems that if you click on the SQL button and scroll down, delete th...

Totals Query Help!
Hi, I have a table with the following fields: Manufacturer Model Value SoldMonth What I'm trying to do is write a query that will give me an average Value for each month, but I want the average to be based only on records where the Model is consistent across all months. So if Model x is missing from one or more months all records for that model would be excluded from all the overall Monthly averages. So the query will only average records where the Model is present in each month. Any help with this would be greatly appreciated. Regards.....Jason It'll help us if you can ...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

how do i open spreadsheet from e-mail
i have win xp home edition. Have a time sheet e-mailed to me. To open it I am told win needs to Know Who created it. Then it tells me microsoft exel but i have no idea what to do next! Do you have Excel installed on your computer? It is a component of MS Office suite and does not come as part of Windows OS. Do a file search for excel.exe and see if you can find it. If not, download and install the Excel Viewer from.... http://office.microsoft.com/downloads/2000/xlviewer.aspx You will be able to view and print Excel worksheets. Gord Dibben Excel MVP On Tue, 14 Sep 2004 19:11:01 -0700...

How to count the maximum number of consecutives?
I need to determine the maximum number of consecutive negative numbers in a column. The column contains formulas that evaluate to a positive number, a negative number, zero or "" (blank). I cannot add an additional column. The following are the results of formulas in cells A1:A11 that need a formula in A12 that evaluates to 3, the maximum number of consecutive negative numbers counting cells A2, A6 and A7. 1 -2 "" "" "" -6 -7 8 -9 "" -11 You're sure you can't us an extra column somewhere?? Even a hidden on...

I have a spreadsheet that in a simplified form looks like Drug Pump A 1 C 4 A 1 B 3 A 1 A 2 B 3 C 1 C 5 I need to determine how many different pumps each drug was on. So I need to return Drug #Pumps A 2 B 1 C 3 The spreadsheets I am working with have about 30,000-50,000 rows, so that may rule out array formulas. I would prefer to not use pivot tables because the entire table is going into a pivot table for analysis. If needed, I can resort the table but would prefer not to. The best layout for my needs would be t...

Counting data
Thank you. Now I want to take the sheet that summarizes the individual dates divided into the 3 times groups - and total them by month. How can I do that without having to manually choose each range for each month? [ Original post in: http://tinyurl.com/69bp2 ] Do a similar set-up in a new Sheet3 In Sheet3 ------------- Put in A1: =Sheet2!A1 Copy over the 3 time bands listed in: B1:B2 : 700, 1500 C1:C2 : 1500, 2300 D1:D2 : 2300, 700 (these will act only as col labels here) Put in A3 : 1-Jan-2003 Format as Custom, type: mmm-yy (It'll appear as "Jan-03") Copy A3 d...

make table query with related tables
I have an equipment maintenance database, I would like to put my inactive (gotten rid of) equipment in an inactive table, and not loose the relationship with the maintenance table. basically make two tables. I did a make table query, made my inactive equipment table. I could not delete the equipment from my first table due to key rules. I then made a relationship from my equipment table to my maintenance performed table. could not make a one to many and enforce integrity due to the first equipment table. I am going to delete the relationships, run a delete query on my inactive marked e...

Highlight keywords in result of perameter query
I have a query perameter "*" & [Keyword Search] & "*" the returns the records based on the keyword. Works great. Is there a way the keyword to be highlighted or change color in the results? Not that I know of. Especially in a query. -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Ryan Tisserand" <RyanTisserand@discussions.microsoft.com> wrote in message news:34DD4D45-C312-4204-B53E-B22300A0613B@microsoft.com... >I have a query perameter &qu...

How do I stop a spreadsheet from Auto-publishing as a web page?
A colleague has taken a copy of a Excel file which was set to automatically publish as a web page each time the xls was saved. The copy is in a completely different location and is going to be put to a different use, however it is still trying to auto-publish when saved. Consequently, it is coming up with an error. The error dialog has 'dont show me this again' type checkbox, but I'd rather remove the problem rather than hide it. How do I stop the auto-publishing? Thanks Chris ...

Count + condition
Hi again! My other problem is that I want to get the number of different variables (text type) + a condition (date) Example: Col 1 AAA AAA BBB BBB BBB AAA CCC DDD AAA EEE Col 2 2004-08-01 2004-08-01 2004-08-01 2004-08-02 2004-08-02 2004-08-04 2004-08-04 2004-08-04 2004-08-04 2004-08-04 I should get the numbers of different variables for each date 2004-08-01 -> 2 2004-08-02 -> 1 2004-08-04 -> 4 thanks --- Message posted from http://www.ExcelForum.com/ Hi if you create the list of unique dates on a separate sheet (e.g. using 'Data - Filter - Advanced Filter') in co...

Query Based Distribution Lists #3
I can create QBDL based on STATE, ZIP, LASTNAME and many other fields. I would like to make a list based on Group Membership. There is a field under USER called MEMBER OF but it does not give me any results when I do a preview. I have many employees who work in multiple locations and need to belong to several different groups even though they have only one login and work out of a single office with the same address etc. So I made them members of groups based on the different location in which they work and wanted to send mail based on this membership. McMurray <McMurray@disc...

Can someone provide me a web site of an Interactive Excel spreadsheet which asks a user some general questions and then uses those answers to develop worksheets behind the scenes??? Thanks in advance! RTP Very general question. Please provide a few more details. Gord Dibben Excel MVP On Thu, 16 Sep 2004 13:57:04 -0700, RTP <RTP@discussions.microsoft.com> wrote: >Can someone provide me a web site of an Interactive Excel spreadsheet which >asks a user some general questions and then uses those answers to develop >worksheets behind the scenes??? > >Thanks in advanc...

using the count function
I have 5 departments and I am trying to count the number of yes votes for each dept, is this possible Sure. It's called Countif. See this article: http://www.officearticles.com/excel/count_using_multiple_criteria_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "barklek" <barklek@discussions.microsoft.com> wrote in message news:1D177E99-6A3B-4845-A067-B31495706CA8@microsoft.com... >I have 5 departments and I am trying to count the number of yes votes for > each dept, is this possible barklek Wrote: > I have 5 departments and I am tryi...

Using single button to run multiple query
I want to use single button on the form to run multiple queries. How it can be done? Where should I write queries and code? One way to do this would be to create your queries, then use the command button to run code that calls each query. Another approach would be to create all your queries, then create a macro that runs all your queries, and call that macro using code behind a command button. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Gajanan&qu...

Missing records using a timestamped field to query.
I'm trying to extract a range of information using a timestamp field through a select query. I'm using Between[Date] and [Date]. It's not caputuring all of the records, specifically the records w/ the 1st and the very last days of the range. I tried doing something like this "Date([Timestamp])" but to no avail. Any thoughts as to why?! Thanks, Michael On Tue, 12 Feb 2008 08:39:02 -0800, Michaelcip <Michaelcip@discussions.microsoft.com> wrote: >I'm trying to extract a range of information using a timestamp field through >a select query. I'm...

Event Log Queries
Ok, so I'm working on querying multiple DC's to look for a specific event ID (645 in the security logs, for computers recently joined to the domain). I thought I had everything figured out, but it's still not working right. Running the script, it just somewhat hangs...not sure if that's just because it's still searching or not and is slow. Let me know if you guys have any thoughts...thanks! Dim objDictionary Set objDictionary = CreateObject("Scripting.Dictionary") arrComputers = Array("DC02", "DC03") iReach = 0 For Each st...

displaying Query results to subform
First--the disclaimer--I am in no way a programmer, but I am working diligently to support my team in any way possible. With that said, I have painstakingly created a simple build SQL string code in Access Visual Basic Editor: Private Sub Command8_Click() Dim SQL As String SQL = "SELECT * " & _ "FROM [BCSG_CARDFILE] " & _ "WHERE ((([BCSG_CARDFILE].[IMPORT_FILENAME]) Between " & _ """" & _ Forms![BCSG_CARDFILE_SEARCH]!cboDateFrom.Value & _ """" & _ " And &quo...

Query to Filter by Dates, Using Form