Counting The Number Of Extra People Working Each Hour?
Aug 19, 2014
I am trying to count the number of extra people scheduled for each hour of the day, from 2 dropdowns in A3:B7
The formula in (1) D11:D26 works, but when the shift passes over the midnight it will not count accurately.
The formula in (2) E11:E26 also works well, but only counts the number of times a particular hour is mentioned, but not the number of extras in C3:C7.
The results in (3) F11: F26 are what I need, which is based on looking at the times from the dropdowns and using the number of extras in C3:C7.
View 2 Replies
ADVERTISEMENT
Nov 18, 2011
Formula that counts the number of people who are on my course and are taking their tests.
I have 5 classes, Class1, Class2, Class3, Class4 & Class5.
Column A details which class the pupil is in.
Column B details the pupils name.
Column C details the state of the course. This is either a DATE which they started, or "Paid" (they have paid for the course but not started), "Not Paid" (havent paid for course and havent started) or "In Progress" (Payment is in the process of being arranged).
Column D details the state of their final exam. This is either a DATE as to which they have finished and passed the exam, "Not Taken Yet" (Havent taken the exam yet) or "Fail" (They failed the exam).
This is where I am having problems, at the top of my sheet I want to summarise each classes statistics, but I am having problems with the COUNTIF. For example:
What formula can I use to add up the number of people in each class who have started the course?
E.g.
=countif(C10:C100," Is a Date ? ") AND is in Class1 ?
What forumla can I use to add up the number of people in each class who haven't passed the test ?
E.g.
=Countif(D10:D100,"Not Taken Yet" & "Failed") AND is in Class2 ?
I know I need an array to work this out.
And finally to really complicate things how about :
People in each CLASS who have STARTED the course (Date in C) AND haven't passed their test (Col D). I understand this is relying on a three part array whereas the others are 2 parts ?
View 7 Replies
View Related
Oct 21, 2013
I work in a computer lab and we have to keep track of how many people sign in (using excel 2010), and how long they were here.
I'm looking for a way to count how many entries were made per hour for every hour someone was signed in.
For example someone signs in at 9:22 am and leaves at 3:34 pm.They were here from 9-10, 10-11, 11-12 etc.. and its getting very tiresome to go through and manually enter a "1" for each individual hour in each cell under the hour.
Is there a way I can feed the in and out times into a spreadsheet and have it automatically count how many people were in the lab each hour?
A
B
C
D
E
1
2
4
2
2
In
Out
8 am
9 am
10 am
[Code] ...........
View 3 Replies
View Related
Nov 12, 2013
Is it possible to take data in format dd:hh:mm and convert to working days - ie. 8 hour days?
For example:
02:12:15 (60 hours and 15 minutes .. or 3615 minutes)
to be converted to:
07:04:15 (7 working days: 4 hours: 15 minutes)
View 3 Replies
View Related
Apr 26, 2014
i am trying to remove some duplicates in a sequence and add new cells for missing ones
View 9 Replies
View Related
Dec 11, 2013
I've put together a spreadsheet look at the time taken for a vehicle to get from one building to another when called. I've set up conditional formats where the timings turn orange after 5 minutes and red after 10 minutes taken. What I also have to show though is who is taking the vehicle and break it down to show any delays to patients. Is there a formula that will automatically tell me when there has been a delay to patients?? Be amazing if there is as it would save me uber amounts of time as i have to collate a whole years worth of data!
I've attached a small sample of what I've done to show you how the spreadsheet looks.
sample.xlsx
View 5 Replies
View Related
Jan 1, 2009
I'm using Excel 2003, and successfully working out start date (T2 in the formula below) plus duration in days (U2) minus any non-working days listed in a seperate worksheet. =WORKDAY(T2,U2,Holidays!C6:C17)
Each 'task' is on a seperate row, and the lead officer is named in Column E. How can I get the work day function to include the non-working days for each officer as well as the global non-working days in the seperate worksheet? I thought about using a vlookup, but that only matches the first non-working day for each officer rather than all of the non-working days.
View 2 Replies
View Related
Jul 28, 2014
I need counting the number of staff within a time period. Unfortunately the schedule structure does not allow application of the simple solutions I've found regarding this which results in this being more complicated. My example is attached. My start and end time are in the same cell. I have used the Left() and Right() functions to manipulate the times. I am unable to count the number of occurrences of between the start/stop times of all my employees in the array. The formula also needs to take into account a manually entered time on the actual day.
Basically:
00:00 if F9:F42 = time is TRUE and I9:I42 = "" then count occurrences of 00:00 in scheduled times F9:F42 + if F9:F42 = time is FALSE, then if I9:I42 = time is TRUE then count occurrences of 00:00 in scheduled times I9:I42
01:00
02:00
"
"
Even when I think I've gotten close the midnight time crossover keeps throwing off.
Schd_05_TEST.xlsx
View 3 Replies
View Related
Nov 16, 2007
My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).
How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?
View 9 Replies
View Related
Aug 1, 2007
I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.
On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.
View 9 Replies
View Related
Oct 1, 2009
I'm using Excel to schedule employees shifts at my restaurant. Column A is their name, Column B is their start time, Column C is their end time. I'd like to be able to count the number of employees working during any given hour of the day to assure I've got enough staffing each hour. I'm thinking it will require a COUNTIF function but I can't figure out how to make it work. I've attached a sample sheet.
View 4 Replies
View Related
Jul 4, 2008
I want to do is to count the number of people under each class, eg. 2300 - primary, 3 ppl, but i gt many many classes in my spreadsheet, do u all have solution for me using VBA
SAMPLE DATA:
2300 - primary
amy
ben
alex
1234 - secondary
Linda
tom
Jerry
Tricia
Mabel
5603 - advanced
Linda
tom
Jerry
Kevin
Calvin
View 9 Replies
View Related
Jun 28, 2009
I have a workbook with a sheet for each day of the month where guest information is entered. There is a column for each item. There is a summary sheet that summarizes the number of members, guests etc. One thing I would like to track is the number of people from ech zip code.
View 9 Replies
View Related
Apr 21, 2014
Count people per their classification between two dates. Spreadsheet attached.
The answer I need to show in column (S) is:
Cell (S4) = 1 (supervisor)
Cell (S24) = 1 (fitter)
Cell (S36) = 3 (rigger)
j906 count.xlsx
View 1 Replies
View Related
Aug 22, 2009
If you look at the attached file in Column E line 47 you will see that it displays
373.97. However if you look in the formula bar it shows 373.9694. I am trying to make it so that the formula bar shows the same number as in the cell. Any idea on how to do this? I have tried everything I know how to do, which is not much.
View 4 Replies
View Related
Jun 19, 2006
I need to add an extra four zeros to a number in a cell - in this case an ID number, so that i can do a lookup from another list. Basically what i have is two lists of ID numbers in a field of a database, in one i have the correct display/format, so that a number would look like 000054454545. In the second list however the number is only shown as 54454545, due to differences in the programs which imported them. I would like to know if its possible to use a function or macro in excel to basically insert the four zeros onto the number ie 0000 + 54454545 = 000054454545 so that i can do a lookup of one for the other.
View 4 Replies
View Related
Sep 4, 2006
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
View 9 Replies
View Related
Jun 1, 2008
I am wanting to take a number (any number) and multiply it by per minute using real time. For example: Say I have 12 apples. I want that 12 apples to multiply per hour.
12x(per 60 minutes)= total
The total will change every hour because the formula will be using real time. I can write the formula for 12x60 but to enter the time, based on real time I just don't know or even if it can be done.
View 2 Replies
View Related
Oct 7, 2012
I am looking for a way to count the number of emails I receive per day and hour, below is a chart that I use to see the data. I had accomplished this with sumproduct, however it severely slows down the workbook processing to the point that it is no longer an option to use it. I extract my data from outlook and format my date and time in the same column such as 10/01/2012 15:00, I work from 7AM until 5PM then all other email are considered after hours and go in the 5p-7a row.
Email count.xlsx
Time
Mon
Tue
[Code]....
View 2 Replies
View Related
Jan 7, 2014
I am trying to calculate the the breakdown time for each hour in the factory. I have the start time and finish time of each breakdown. There are 8 hours in a shift starting at 6:45. So hour 1 would be (6:45-7:45), hour 2 (7:45-8:45). If the machine breaks down between 7:06 and 8:20 what will be the amount of time lost in each hour? In this example it would be 39 mins in hour 1 and 35 mins in hours 2. How I can achieve this result?
View 4 Replies
View Related
Apr 17, 2009
I am trying to count the number of cells that have a red fill, this is the formula
that I am using and it is not working.
=COUNTCOLOR(A1:A10,255,FALSE)
I want the cell to show 2 if 2 cells are filled with red, does this make sense.
View 14 Replies
View Related
Sep 19, 2008
I'm trying to Count the number of Entries on a Sheet that match an Hour. Looking through the availiable functions i found COUNTIFS, which is exactly what I want. However, when I try to compare the Hour values within the COUNTIFS arguments, there is an error.
This is the function that I figured would work here:
=COUNTIFS(HOUR(Sheet1!G:G), HOUR(E6))
which should count all entries in column G where its HOUR matches the HOUR in E6 (all are time format). I do realize that in the example above there is only one comparison made and i'm using COUNTIFS instead of COUNTIF, but i'll be adding other comparisons to it once i get this first comparison working.
View 9 Replies
View Related
Dec 2, 2013
I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.
Attached file, defined example and detail.
View 1 Replies
View Related
Feb 24, 2014
Ok so I am trying to streamline the Shirt ordering process at my work. I have made a table with Employee names as Column A, then Row 1 is shirt sizes from small to XXXL. The Cell values are going to be the colors people want. I want to total the number of each size/color combination. But someone may want more than one of the same color. For instance you would enter "BK, BK" if you wanted 2 black shirts of that size. This is where the issue with COUNTIF comes in. If a cell has the same value twice, it only counts it as one because counts the cells with an instance of a value, not the actual count of instances. Is there a way to make COUNTIF or a better function for this?
View 6 Replies
View Related
Jun 2, 2014
I have an attendance sheet for our Company Employees.
This attendance sheet shows the salary of the month from the total basic salary / hour and overtime / hour of each
employee.
I found a problem in this sheet. When i reduced overtime charges in the last of this sheet so the Total Salary of the month
was not changing. I couldn't found any mistake in formulas.
View 1 Replies
View Related
Jan 29, 2014
I have a downtime tracking spreadsheet that we want to be able to track the downtime on the line. However, we only want to count the working hours, not overnight, etc. I have added the weekend day work hours and Saturday work hours but haven't been able to figure out how to connect them into a formula with the down time.
View 1 Replies
View Related
Nov 22, 2007
I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....
View 9 Replies
View Related
Aug 13, 2009
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
View 2 Replies
View Related
Jul 10, 2014
I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.
I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.
------ UPDATE
In response to using CountIFS I have also tried-
=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.
View 10 Replies
View Related
Jan 5, 2007
I need to know wich formula or VB method, I have to use to count the number of cases in a range of colomns and rows C4:G107, have the same number. For eaxample how many times do I have number 1 and so on...
View 9 Replies
View Related
