I have a sheet in which in Coloum A I have manager names, then in Colum B I have list of Employees reporting to that manager, then in Column C, I have total login time of each employee which should be => 8 hours. In a new sheet, I want to get the count of employees next to the each manager's name who login time is less then 8 hours.
View 3 RepliesI am trying to get a count of supervisors and employees at any given time during a few day span. I think maybe a pivot chart might work but I'm stuck.
In my example I have a number of supervisors and employees in separate groups. I would like to type in two dates and two times for an in between time and get the total amount of employees working during a certain time.
Count of Employees at any given time.xlsx
I have been trying to work out a formula for capturing the number of patients in the hospital at half hour time intervals. There are a lot of formulas for capturing this information within a 24 hour period however not a lot of information when the Length of Stay or episode time is +24 hours.
As you can see in my example spreadsheet below, some of the patients stay for 244 hours (row 9).
The outcome that I am looking for is that a 1 is placed in all of the time slots when the patient is there. For example if they arrive on Jan 1st at 2.15 and leave on Jan 3rd at 10.30 all of the time slots in between would have a 1 placed in them.
I have been playing around a lot and think it is probably only possible if you set it up as I have in the example i.e. by having the date running down and the time running across.
How this could work? I have tried SUMIFs and SUMPRODUCT formulas which generally work for Jan 1st but then go wrong for any date after that.
I am trying to do a timesheet spreadsheets that lists employees clockin and clockout times
Name Start End num hours worked
Cory 02:00 04:00 2
Jack 23:00 05:00 6
Fred 10:00 17:00 7
and then go through the list and and count the number of employees in a certain range.
time range number of employees working
05:00-06:00 1
06:00-07:00 2
07:00-08:00 6
08:00-09:00 5
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00..............
I am struggling to find a formula to calculate hour:minutes between the following:
Cell
A1= 04:00
B2= 17:00
I am wanting to calculate the hours between 06:00 and 16:00 and then the hours between 16:00 and 06:00 the next day
ie: 06:00-16:00 = 12;
16:00 - 0600 = 3
Im doing a spreed sheet to calculate employees hours. I have the employees names in column R and their hours in column S. example R5 = Mike S5 = 8. I need to search column R for all the mike's and total his hours, then move to the next employee and so on... I have already sorted the employees names so that all the names and hours correspond and starting in column R5 and S5 until the proceeding row is blank and then paste the results in column B5 (employee name) and D5(total hours)
View 4 Replies View Relatedi am trying to get totals from data and am using 2 different times to count from (eg. i am trying to find how many cells contain times in the 6:00 am to 2:30 pm range in the same column...I can't seem to be able to count these correct.
View 5 Replies View RelatedI have to calculate employees work hours for overtime.
in the timesheets:
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 5:00 Regular <----- to make 40
5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime
How can I do this?
I want to make a command button for macro to perform this.
I need to create a formula that assumes 40 hours = 1 FTE (full time employee). As an example if I have a total of 100 hrs I need to know how many employees to hire. So in this case it would be 2.5.
View 1 Replies View RelatedI'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 RelatedNeed VBA macro to read and sum data from multiple worksheets
The “summary” worksheet contains list of all employees. I need to update the total number of hours for each employee by scanning all worksheets in the workbook as follows:
Once I click on a "update" button, I need to scan all worksheets (I have one worksheet for each week) which contains the total number of hours for employees by week. I need to calculate and add the total number of working hours for each employee then move to the next worksheet and so on ..
Once all worksheets are all scanned, the final total of working hours get posted in front of the employee name in the assigned field.. I need to do this for all employees. I also need a macro to create a new worksheet.. I attached an example with little description inside .. Labor Detail Job to Date-2.xlsx
I need creating a formula that will tell me the total number of employee hours worked during a certain hour (6:00 AM) for a particular department. Some employees clock in at 6:00 AM, some clock in at 6:30. Here is the current formula I'm using which only tells me the amount of employees in the department not how many hours worked . I have three employees that clock in at 6:00AM and one at 6:30AM, the total I'm looking for is 3.5 but my formula gives me the result of 4.
C5:C1446 is a list of departments
T1503 is the 6:00 AM
D5:D1446 are the Clock In times
K5:K1446 are the Clock Out Times
=SUMPRODUCT(--($C$5:$C$1446="Shipping"),($T$1503>=$D$5:$D$1446)*($T$1503
Maybe it is the layout but I was trying to keep it simple and just put hours into a cell for each employee.
I have 2 employees per day, one in the morning and one in the evening. How to get excel to add the hours for each employee at the bottom per month. Simple right?
Here is an example of what I thought would be so easy (first week in excel spreadsheet format)
sun mon tue wed thu fri sat
emp1 5 emp3 6 emp5 5 emp1 4 emp3 5 emp5 5 emp1 6
emp2 5 emp4 5 emp6 5 emp2 5 emp4 5 emp6 6 emp2 6
Is there a way to associate cells to look at values for each employee and add all the cells for the month?
I am usuing INDEX and MATCH to pull back employees hours from a master sheet, I am asking excel to Index, matiching firstly the agents name and then the day of the week (Formatted: Mon,Tue,Wed,Thurs,Fri,Sat,Sun). This works perfectly for every day of the week using the following formuls except for Sun where it returns #REF!
=(INDEX(Hours!$B$4:$G$166,MATCH($F$1,Hours!$A$4:$A$166,0),MATCH(C7,Hours!$B$3:$H$3,0)))
When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?
View 3 Replies View RelatedHow to get the Hours of two different time
A1
27/06/2014 11:30
B1
30/06/2014 18:14
C1
Ans: Hours of these 2 dates with time.
i just need to get their Hours.
I am not sure how to go about this but here is my problem: text in cells a40:A147 containing card number this: -01- and cells h40:h147 containing and "x" if the card -01- is closed. Now I want to count all the -01- cards that do not have an x in the h40:h147 cells and I need to count all the -01- cards that do not have an "x" in the h40:h147 cells.
View 2 Replies View RelatedI have a spreadsheet designed for softball schedule for an entire league and am trying to come up with a way to count certain information so that I can figure out if the schedule is balanced. What I'd like is for a way to sum the number of games each team plays on diamond 3 or 4 and to sum number of games at 6:30/7:30/8:30
View 2 Replies View RelatedI've got a worksheet that deals with dates and time. Similar to a checkbook, I need to take the balance and subtract what's taken from that. But the writer copied this from another software program and it's in this format. How do I convert it to a time format? Or is that what I really need? Right now the taken and balance are both in a general format.
I just need to know on any of the given days what the balance of this person's time was in hours and minutes.
I am putting together an email reporting template in Excel that pulls in data from various sheets. The reason I'm doing this is because we need to brand the reports as our own, but our email service provider doesn't offer this capability. As a result, I have the report template saved on one worksheet, and I have copied and pasted other bits of the report that I need onto other sheets.
I need to create an area graph of the number of recipient opens against time (a response graph basically). So along the X axis I will have time periods (0-1 hours, 1-2 Hours up to 1-2 Days, 2-3 Days etc). Along the Y axis will be the number of recipients who opened the email within that particular timeframe. I can produce the graph easily enough when the data is in front of me, however, getting the data in the right format is proving tricky.
The list of open times is stored in column 'D' on a sheet called 'OPEN TIMES'. The reference time (time the email was sent) is stored in cell 'A60' of sheet "Full Report". As I see it, I should be able to use a COUNTIF formula to total the number of opens that meet a criteria (within a certain timeframe). So, this is the formula I tried:
=COUNTIF('Open Times'!D:D,('Open Times'!D:D
I have been looking for a way to count the number of cells in a column that do not contain the word "No." I used the countif formula to count the cells that do contain "No" but I need a formula to count cells that contain anything but the word no.
View 11 Replies View RelatedI have devised a simple formula when a member of staff enters a date and time into a cell it starts deducting from todays date and time. ie:
Cell A1 - Date and time is entered by staff
Cell B1 - Is the above cell +24hrs
Cell C1 - NOW()
Cell D1 - B1-C1 Formatted in hours ( [h] :mm:ss (Like a count down clock)
The problem i have is that i cannot get Cell D1 to show minus ours it just goes to negative and shows continous #####. Is there any way i can get Cell D1 to show the hours it has gone minus by.
I need to enter a formula that calculates the time a report is received from the time it was recorded in our database. therefore, it needs to exclude non working hours. here are the fields:
A1 2008/10/10 16:30
B1 2008/10/11 09:30
C1 8:00
D1 17:00
A1 = report received
B1 = reported recorded in db
C1 = work day start time
D1 work day end time
Where the answer should = 2 hours.
I am not an experienced excel user and so far the only formula I have now is: =TEXT(B1-A1, "d:hh:mm")
And how do I account for weekends?
I am creating a employee time sheet.
I am doing calculation.
I used total hours formula is =(I5-B5+(I5<B5))*24
and also worked hours formula is =((I5-B5-(I5<B5))-(D5-C5+(D5<C5))-(F5-E5+(F5<E5))-(H5-G5+(H5<G5)))*24
Problem is don't show the correct time of total hrs & worked hrs
Here i am attaching my timesheet format.
What formula will count the number of times the value J3 appears in E2:E400. BTW...the a-g is formatted as a table. excel 07
View 3 Replies View RelatedI am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...
=COUNTIF(G1:G100,"AND(>10,<20)")
Currently, I am using countifs to count data within a specific range if it meets a criteria. Now I want to add to the formula. I need to count the cells within the specific range meeting the existing criteria I already have but only if one cell is greater than another cell.
This is what I currently have: =COUNTIFS(Report!$F$4:$F$1048576,">=8/1/2013",Report!$F$4:$F$1048576,"=8/1/2013",Report!$F$4:$F$1048576,"
We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.
Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.
In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.
TYPE
MTD Closing Date
Status[code]......
Im trying to figure out the formula to comput hours:minutes between time on different dates a couple of days apart.
B12 = Date
B15 = Time (on date in B12)
Previous date and time are in AA9. I have been trying to us =SUM(B12+B15)-(AA9).
I'm logging failed password attempts and need to find the latest time logged for a single user (when user has failed 5 times).
So the 5th attempt, I need that time
So the usual - Application.UserName in the next free cell in a range and time stamp in the next cell.
What I've tried is:
Say log range is A1:B500 and
A=Username, B=Time stamp
In column C I've nested the two together
(this has more code but not required to get this working)
In C1
Code:
=A1&" "&"live"&" "&B1
So this will bring back "Username live 23:12:12". Now, say there is 5 of these (all with different times)
How do I find the latest time for that user?
Code:
IF(COUNTIF("USERNAME LIVE"&"*")=5,[find the latest time in that list",""
Just to wind you up some more, other users name will be logged and muddled up so it's literally -
"Excel - if a user has failed 5 times then give me the time of the 5th attempt"
I've tried a mixture of COUNTIF, MAX, LARGE, TIMEVALUE(...)