Calculating shift patterns (Sorry can't be more specific)

I really hope that someone can help me. I'm at the end of my wits tryin
to work this one out. 

I have a time sheet


M T W T F S S (ETC for 3 months)
PERSON A    E E  L M E O O
PERSON B    O O L M M E E


E Shifts
M Shifts
L shifts
O (Off) 

I want a formula that could help calculate how many people are on 
certain shift on each day.  

So, E shifts for M = 3, for instance, so that each time I add a shif
it automatically calculates the number at the bottom. 

Also and I know this is cheeky...but does anyone know how I can tall
how many L shifts someone does in month etc. To keep an automati
check.  

I would be most grateful if anyone can help with this

--
Message posted from http://www.ExcelForum.com

0
7/12/2004 9:17:19 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
488 Views

Similar Articles

[PageSpeed] 36

Hi!

The formula  =countif(A1:A100,"E") will count how many times the entr
E occurs in the range A1:A100.

The same countif approach will work within a row (for an individua
person) but you will need to be more precise as to how you are definin
periods and whether you want it to be rolling, fixed months, four week
or what.

Al

--
Message posted from http://www.ExcelForum.com

0
7/12/2004 9:54:50 PM
I gave it a shot.  Just tweak it to your requirements. ;

            Attachment filename: shift patterns.xls            
Download attachment: http://www.excelforum.com/attachment.php?postid=61568
--
Message posted from http://www.ExcelForum.com

0
7/12/2004 11:03:36 PM
Reply:

Similar Artilces:

Processing a specific user message during a non UI function
In my MFC application if a data item is added, a user defined message is posted to update a map. I've written a new feature which imports data in a single thread, ignoring all windows messages until the end. This is necessary otherwise the user could continue using the UI and probably crash the program. However I now realise that I need to process the user defined message during the import, but ignore the other messages or the things can get out of sync. I've looked at PeekMessage & PumpMessage, but they seems to be wasteful of resources and tricky to target at a specific m...

Can't eliminate flicking of chart on every calculate #2
I am using Excel 2002. I have real time data being charted , and I manually calculate ranges and sheets as needed. This is not a problem. I have recently added charts to plot this data. Every time there is a recalculation anywhere on the sheet (even areas not being charted) the charts flicker. Since there is new data multiple times per second , this is a problem. Yes, I know about Application.DisplayAlerts = False, and tried inserting that before EVERY calculation call. I even went so far as to use the LockWindowUpdate api call, which freezes the entire app. Unfortunatel...

Referencing a "calculated" column
Within a query, I created a column based on a fairly complex calculation referencing a number of fields in the database and a number of fields in the form that uses the query (this query is behind a ListBox on that form). I've given the "created" column a name (let's call it XYZ). The result is fine in that the content of that column in the ListBox is correct. I now want to reference the contents of that column in a second "calculated" column (further to the right, and beyond what appears in the ListBox). This second column contains a couple of IIf 's a...

How do I calculate duration between two dates and times in excel?
I need to calculate the difference in hours between two dates and times. For example the difference in hours between 11:25 on 23/07/2004 and 18:00 on 06/08/2004? The dates and times have already been determined by the start and end of a particular event. Thanks, Let's say the 23/07/2004 date is in A1 and the 06/08/2004 date is in B1 C1 =(B1-A1)*24 gets time in hours. Ensure that C1 is formatted to say NUMBER with 2 decimal places. "Robin CSM002" wrote: > I need to calculate the difference in hours between two dates and times. For > example the difference in hours...

Hard to explain, but specific e-mail causing problems for my clien
I don't know how to explain this or label it, but let me do my best. This week, about half my users are experiencing weird errors with Outlook when they first get in. What happens is that their Outlook stops responding when they check their email and it only happens when they first get in in the morning. I've been trying to figure out what is going on and it has been difficult. However, I might have found something that could be causing the problems. We are a mortgage company and every morning, a person in our operations department sends out 3 Rate sheets in .pdf format. They ...

highlight specific cells in array #2
Domenic Wrote: > Try the following... > > 1) Select your range, in this case C1:F20 > > 2) Format > Conditional Formatting > Formula Is > > 3) Enter the following formula: > > =C1=$A$1 > > 4) Choose your formatting > > 5) Click Ok > > Hope this helps! Great! So easy, but you have to know it! Thanks -- joke ----------------------------------------------------------------------- joker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1621 View this thread: http://www.excelforum.com/showthread.php?threadid=27...

UK Corporate calculation
Can anyone help me. I am building a business plan including financials cash-flow, balance sheet and p&L. I would like to accurately state corporate tax in the p&l but cant get my head around the forumula needed. Brgds Andy I'm not familiar with UK Corporate tax, but you may find the formulae at http://www.mcgimpsey.com/excel/taxvariablerate.html helpful. In article <yszib.155$hr2.47@newsfep1-gui.server.ntli.net>, "andy hamer" <andrew.hamer@ntlworld.com> wrote: > Can anyone help me. I am building a business plan including financials > ca...

Create task with a specific due date from workflow?
I have a user defined field on an opportunity, lets call that "Estimated Required Date". When the date field is entered that triggers a workflow rule that creates a task for someone to build the estimate for the job. How do I create that task such that the task due date is the "Estimated Required Date"? The workflow forms seem to only allow number of hours, days, etc. from the time of Task creation and even that seems to require a hard-coded value. Thanks in advance for any help. Hi, Using the tools that come with CRM, this is not possible. The forms to create Workf...

What should my calculation settings be?
I have made a macro which FILLS DOWN formulas into an MS EXCEL 2000 Spreadsheet. My macro calculates the formulas except some have " #NAME? " as a value. The crazy thing about this is that when I go into the status bar and hit enter the formula is calculatedthe exact number comes up!!!! I've heard about Microsoft issues, is this another example? What should my calculation settings be? I'm not sure if manual, automatic, iteration, or calculate before save will help me out. Please help. Thanks ...

How to create a calculated field formula based on Pivot Table resu
Hi, I have a simple Pivot Table consisting of: Row Section (column A): Name Column Section (column B, C and D): Courses (X, Y, Z) Data Section: Is the MIN test scores for the course. For Example: John 8 5 7 I am trying to add a Calculated Field to the Pivot table to SUM all of the test scores shown in the Pivot Table results. The problem is that the calculated field SUMs ALL of the test scores in my data, not just the ones shown in the Pivot Table (which is the MIN for each course). What I Want (the SUM of the test scores shown in the Table): John 8 5 7 20 What I Get (the SU...

Automatic Calculation Issue
Hi, I have a spreadsheet that I have created that includes many formulas, conditional formatting, and links. I brought up the spreadsheet today and nothing was calculating automatically. I went to the Tools drop down and under Options I noticed that the Calculation method changed from Automatic to Manual. I didnt change this, does anyone know why this would have changed on it's own?? Thanks, Dave Probably no-one. Excel takes it's calculation state from the first workbook you open after launching an Excel session. It may be that your personal.xls, or a timesheet file you ...

My Excel file is not calculating!
Good morning all, I have an Excel file with two worksheets: Report and Data. The worksheet Data has data where the data are stored, and the worksheet Report is retrieving the data from Data to prepare a report by entering an ID number. The data are retrieved using the vlookup formula in the Report worksheet. The worksheet Data has 130 columns and 14,000 rows (up to) of data. The way it works is I enter an ID number in the worksheet Report. Then I press F9 to do the calculation and the worksheet Report will start to populate the data. Due to the size of Data workshe...

SQL for specific pattern of output?
The following example is a simplification to make my question less complicated. I have a database of 3 people: Brown, Jones, and Smith. Each person in the DB has 2 fields associated with their name field: Field “A” and “B”. The values in A and B are either 0 or 1. The following SQL string extracts a query as shown below. SELECT tblPeople.LName,tblPeople.A,tblPeople.B FROM tblPeople Brown 1 0 Jones 1 1 Smith 0 0 But the IT department wants the data as follows: Brown A 1 Brown B 0 Jones A 1 Jones B 1 Smith A 0 Smith B 0 Is...

Display a calculated number as text or words?
I have a query that calculates the number of days to complete a job. In other words it subtracts end date from start date. In a report I want to display the number of days in words. 1 = one, 2 = two, and so forth. I think this will point you in the right direction: Convert numbers into words (dollar values) How to print dollar values out in words: Check http://www.mvps.org/access/modules/mdl0001.htm at "The Access Web" -- Hope that helps! RBear3 .. "JanT" <JanT@discussions.microsoft.com> wrote in message news:2CD08311-91AA-47CE-B9F1-CF022ACB6138@microsof...

Calculate age
Hi I need a function to calculate the age of a person starting on birthday. Thank you Luis Jorge Hi Luis =DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))) where A1 is the birthdate and B1 the date that you want to calculate the birthdate at - if you want to use the current date you can either put =NOW() in B1 or change B1 in the formula to NOW() Hope this helps JulieD "Lu�s Jorge" <op202966d@mail...

Money unable to calculate correct mortgage payment
Hello, I am new to microsoft money. I am trying to set up my mortgage payments in this program, but when I enter the data concerning my loan it never calculates it correctly. It is a new loan so I thought it would be easy since I am entering the data before I have made my first payment. Does anyone have any suggestions in how to correct this? Thanks If it is a car loan, interest accrues from the day you buy the car to your first payment. Add this to the balance financed and recalculate the loan. You should get close.. >-----Original Message----- >Hello, >I am new to m...

Query 1 of 3 exchange rates for specific date
I am a new Access user and am trying to query the correct rate for the right currency on a specific date. My tables are as follows: tblRecDetails tblExchRate RecDate Date Currency USRate Amount YenRate EuroRate For an example: I received US$100 on Jan 29-10, US$150 on Jan 30-10 and Euro$150 on Jan 29-10. I need to find How many CDN dollars I received for each day. Can someone please help??! Thank you in advance for your assistance. Can I suggest altering tblExchRate so it has fields like th...

Pivot table- Calculated Field
Please assist I want to show the % difference for the last dates entered. item # 12/12/04 12/13/04 %Change item1 100 110 9.1% item2 24 35 It places this date in all the columns: 01/0/1900. Thanks Gwen If you format the cells as percentage, you may see the correct results. To format them, select the column with %Change Click the % button on the formatting toolbar. Gwen wrote: > I want to show the % difference for the last dates entered. > item # 12/12/04 12/13/04 %Change > ...

Shift and number
Hi, This started happening while I was working in Excel 2000, I tried to use the equal sign "=" in a formula and instead of getting the equal sign I was getting Pts instead. And the ")" was giving me something different. This only happens in Excel. Anybody has had this problem? Thanks Hello No idea why this should happen in Excel. But you can take advantage of Excel's CODE and CHAR functions. If A1 contains "=" then Code(A1) returns 61. To enter an equals sign from the keyboard, Hols down the ALT key and using the number pad type "61" then...

calculate data from a different file
Is it possible to have a cell return a value from another file that is saved on a server without that file being opened. Here is the formula I am using: =COUNTIF(OFFSET('P:\Manpower\[Copy of Manpower 10-25 to 12-30.xls]Daily MP'!$A:$A,0,MATCH(F$3,'P:\Ohio\Manpower\[Copy of Manpower 10-25 to 12-30.xls]Daily MP'!$5:$5,0)-1),"*" & $B5 & "*") but all I get is #VALUE! if this file is not open on the PC I'm using. ...

Stopping Excel macro pattern recgonition
Hi all, I have an Excel spreadsheet with a macro that I use to load data into a third party program. The problem is that when the macro runs after a couple of repetitions, it recognises a pattern and loads all of the data from the spreadsheet and then tries to dump it into the third party program. The macro then starts to go through at high speeds, not giving itself time to do the tabbing and alt-tab between buttons/programs. Is there some way to cripple the pattern recognition function in the macros? I am using the sendkeys function. Macros don't start 'recognising patterns...

Calculating differences in dates
I want to calculate the difference between todays date and dates in the future in days, hours and minutes eg Today 30/06/2005 10:37 Future date 03/07/2005 11:48 Difference 3 days 1 hour 11 minutes What formula should I use and how should the cells be formatted? Thanks for any help -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=...

Calendar and Calculator Controls
Ladies and Gents I am trying to gain acces to calendar and calculator controls which are not access version specific as my program is to run on different versions of access. Any ideas or pointers? Regards Tom Tom wrote: >Ladies and Gents > >I am trying to gain acces to calendar and calculator controls which >are not access version specific as my program is to run on different >versions of access. > >Any ideas or pointers? > >Regards > >Tom Calendar - I would check around on the internet... I think Stephen Lebans wrote one. I know...

How to provide plugin interface (command design pattern) in VC++ 6
I need to provide a plugin interface in my application. On the basis of selected plugin, the excution is transferred to the plugged in code. I see it very easy in .net by using reflection. Even in c++ i can use COM and IDispatch for this .... but the problem is ... the plugin could call various methods of the exe as well to get the state and other operations ... how can i let COM call my exe methods .... should i pass some sort of interface to COM. REMEMBER: I donot want my exe be compilied again and again with the new plugins been added .... Now let me explain what my exe does ..........

How do I put in a formula in Exel to calculate a persons age?
I have a formula in for someones age but it shows me their age that they will be this year. How do I get it so it shows the correct age and then changes on their birthday. See this. Scroll down near the bottom of this page: http://www.cpearson.com/Excel/datedif.aspx -- Biff Microsoft Excel MVP "boofire19" <boofire19@discussions.microsoft.com> wrote in message news:A6A926FE-3799-4A00-BE0A-2165F5766FF9@microsoft.com... >I have a formula in for someones age but it shows me their age that they >will > be this year. How do I get it so it shows the ...