I need to Find Out the Number of people staffed per interval I used to use a formula like this in order to calculate this it takes in to account Lunches.
=SUM(IF(A8>=$B$2:$B$6,IF(A8=$E$2:$E$6,IF(A8
I am working with the following time sheet/card (attached) and it works great when the person goes to lunch, but when they skip lunch I cant get it to compute correctly. I set the formula back to original state because it was just getting more and more confusing. Additionally I tried to adjust it for working overtime (more than 8 hours in a day) and double time (more than 12 hours in a day and kept getting errors or incorrect results).
View 9 Replies View RelatedI have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
QQ截图20140429104406.png
I've been trying to create a Staffing calculator for a call center. Basically the calculator should be able to add up the number of agents for the next 18 intervals based on the login time that is entered by the user. I've just outlined the functioning below:
Suppose 10 agents login at 8 AM (thus logout at 5 PM), the intervals right from 8 AM till 5 PM should show up the 10 agents. Now suppose 10 more agents login at 9 AM, we would then have 20 agents logged in till 5 PM (since agents logged in at 8 will logout at 5) & the remaining 10 till 6 PM. Thus if we have 10 more agents logging in at 10:30, we will have 30 agents till 5 PM, 20 till 6 PM & 10 agents till 7:30 PM & so on. I have attached an excel file to explain the example & the way the calculator has to be built. It is preferrable that the cells containing the login time aren't fixed, but the user should be able to input any login time in any cell.
I would like to create a new more effective quick staffing board for work.
Name Start Time Finish Time Break Allowance Break Time 8 (then time period from 6 till 2300
BUT when I enter say a shift of say 09:00 17:00 I need this time period to block out on the board, hard to explain ...
how to compute for staffing requirement with turnaround time?
View 2 Replies View RelatedAttached is a sample schedule form. I recived the data on the "Working" sheet in a Word file that is emailed monthly. THis shows a full month of arrival/Departure and passenger counts by Time of Day. I need to develop
1. a driver schedule that would indicate the time the driver needed to be at the station and how many passengers to collect. There could be both and arrival and a departure trip at the same time so this would also need to be considered. The sample data shows when passengers arrive and when they are expected to leave. Some will leave the day they arrive some the next day. Ideally I would like to be able to plan the number of trips to the station per day and at what times, whether it was an Arrival, Departure or a combo trip delivering departing passengers and collecting Arriving passengers on the same trip.
2. A housekeeping schedule also needs to be created showing how many rooms need to be available for passengers and at what times. It would also need to indicate if the passenger arrives and departs on the same day or if they stay over.
i am trying to lunch screensaver using this code, which worked with Excel 2003, but not with Excel 2007 which i am using at the moment. when excution comes 2 :
VBA.Shell "C:WINDOWSsystem32ss3dfo.scr /S", vbMaximizedFocus
it tells file not found. any idea how 2 make it work in Excel 2007 ?
Sub RunScreenSaverProgram()
'*To show the screen saver
VBA.Shell "C:WINDOWSsystem32ss3dfo.scr /S", vbMaximizedFocus
End Sub
Basically I have a timesheet as follows:
Basic Hours Worked
Overtime Hours Worked
Day
Time In
Time Out
Hours Worked
Time Decimal
Lunch Hour
Number of hours
Mon
8:56:00 AM
4:50:00 PM
07:54
7.90
[Code] .......
To determine the hours worked from the times input I am using the following formula:=
HOUR(D65)+MINUTE(D65)/60
These formulas seem to be working fine but what I am stuck on is deducting and adding lunch hours and overtime hours.
The first problem I have is for example if an employee were to type the figure 1 in the Lunch Hour column for each day lunch is taken I would like it to be subtracted from the bottom total.
Also if any overtime were input on any day I would need it added to the totals.
I work at a call center and I have to report absent time for my agents.
I am working on a template that was previously saved, however, the absent hours do not account for when the person has not been to lunch yet. This is usually done as a manual update.
There are three main situations that I need my formula to account for:
1) Person that has been absent all day
2) Person that has been absent part of the day (after lunch)
3) Person htat has been absent only a few hours (before lunch)
Lunches are 30 minutes long
Whenever a person is absent or late, we put their time in two columns called ABS in and ABS out. It basically marks the start and end of their absent time.
ABS IN is when their schedule would have startedABS OUT is when they arrived if they were late, or their out time if they were absent all day.
Here is what my spreadsheet would kind of look like... I need to know how many absent hours there were to calculate the actual worked hours.
I cannot simply deduct abs out minus ABS in because then I am not factoring in the lunch times.
This is how my spreadsheet roughly looks like:
Agent
SCH TIME IN
Lunch
SCH TIME OUT
ABS TIME IN
ABS TIME OUT
Total Sch Hrs
ABS Hours
Actual Worked Hrs
Person absent all day
8:00
12:00
16:30
8:00
16:30
8.00
Arrived late (before lunch)
8:00
12:00
16:30
8:00
10:00
8.00
Arrived late (after lunch)
8:00
12:00
16:30
8:00
13:00
8.00
On Time
8:00
12:00
16:30
8.00
I am using excel 2010 and I am trying to find a formula whats going to give me added break with a set time but anything over a set time doesn't. I want it over 10:58:00 and under 11:32:00. I've got the numbers and tried with a formula but all whats happening is that all the data is adding 30mins break to the end time.
The sheet is using live times and data so the start times vary as do the due time.
I am trying to get the start time and finish time for breaks.
I have 4 columns Name, status, start, finish. In the name column there are multiple names, under status there 6 different status's. and under the time columns there are in and out times dependant on status. I am trying to us index match match. I have tried vlookup and hlookup as a nested function. I keep getting errors.
NameStatus MessageStart TimeFinish Time
Cameron WilliamsTech All & Billing12:31:4812:31:52
=index(starttime,match(name,namefield(match,"on lunch",starttime)))
[Code]......
I am creating a worksheet where employees schedule work throughout the day
eg task 1 will be start time 9am, finish time 11am. The result is 2 hours to do the task.
The problem i'm having is that I need to do a calculation that if a task includes lunch then the result takes an hour away from the task time.
eg task 2 will be start time 12pm, finish time 3pm, excel displays 3 hours, i need it to say 2 hours because 1-2pm is contractual lunch.
I can't just do a day formula that says minus 1 from total as this is task orientated and will vary depending on start and finish times of each task.
If a person does a full day I want the formula to calculate how many hours minus 30 mins for lunch i.e. 7:30 - 4:00 = 8 hours. I have done this and it works fine, the problem comes when a person does a half day i.e. 7:30 - 11:30, this will read 4.5 hours. If this is the case I don't want the 30mins deducted so it should be 4.0 hours worked.
I have high lighted a column in the work sheet (yellow) whereby the user puts an 'x' in the appropriate cell if they are working a half day.
The probem is I cannot incorperate the two formulas in one cell (the Actual hours cell) I have the one formula in the actual hours cell and the other bolted on the end thus giving two different readings.
I need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):
The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.
Ex1:
Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
will be equal to:
Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
Ex2:
Startdate 06-03-2013 12:01
End date 06-03-2013 14:28
Time Difference 01:29
I would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this.
The parameters are:
A1 = Start time 08:00
B1 = End Time 17:00
A2 = break lunch 12:00
B2 = back from lunch 13:00
Task parameters
A5 = start date 01/03/10 (entered manually)
B5 = start time 10:00 (entered manually)
C5 = duration 02:00 (hrs entered manually)
D5 = "end date" >>> (to be calculated exluding breaks and holidays)
E5 = "end time" >>> (to be calculated exluding breaks and holidays)
the next line should be filled in automaitically according to the hours needed and the previous end date & time
A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays)
B6 = "start time" (after line 5: to be calculated exluding breaks and holidays)
C6 = duration 14:00 (entered manually)
D6 = "end date" >>> (to be calculated exluding breaks and holidays)
E6 = "end time" >>> (to be calculated exluding breaks and holidays)
and so on for every new line down.
In a single column of random numbers how do I count the interval since the last occurrence of that unique number?
View 9 Replies View RelatedI have geological readings "W,X,Y & Z". For every drilled hole I have these readings recorded for individual intervals (i.e. 1-2m, 2-3m, etc.)
However, the intervals have now been grouped together based on other (unlisted) specifications.
Now I am tasked with averaging these readings "W,X,Y & Z" for the decided groupings. For example.... if intervals 1-4m have been grouped for the first hole, what is the average reading of "W,X,Y & Z" over this new interval?
I have attached a file which displays the layout of the spread. To the left is the original data, and in columns I:K are the new 'groupings' of intervals. Ideally, I would like to generate the average table to the right of the original data (highlighted in green).
So basically, I need some vba code to average the readings in the 1st table conditionally to correspond with the grouped intervals in the 2nd table.
I want to make a formula that returns a value of a interval, this value is estimated from a serial of 10 intervals with up and down value.
View 1 Replies View RelatedI want to write a macro to copy A2:A25 and paste the same range in the next blank cell upto Row no 21384. I have to do it 891 times.
View 5 Replies View RelatedI have a list of times, and I need to work out a way to establish what time interval it applies to, using a function. In production, this will be used over hundreds of entries at a time, but for the sake of example I'll cut it down to 15 times:
17:28:35
16:11:14
17:08:20
19:21:51
15:29:01
15:31:45
14:32:24
13:39:51
15:44:41
16:52:38
20:17:37
13:26:05
15:45:01
20:12:24
12:53:26
Now, there are 27 different time intervals there times can fall into:
1: 6:00 - 6:30
2: 6:30 - 7:00
3: 7:00 - 7:30
4: 7:30 - 8:00
5: 8:00 - 8:30
6: 8:30 - 9:00
7: 9:00 - 9:30
8: 9:30 - 10:00
9: 10:00 - 10:30..............
So, what I'm looking for is a formula that will match up the time to the interval. For example, it would look at 16:52:38 and output that it falls within interval 20.
I am making a spreadsheet to control my house expenses, and for that matter I would like to have the total sum of expenses for each month, but I am not quite sure how to do it.
I attached an image to better illustrate my question.
I have two persons in my household, and I use this spreadsheet to calculate how much we owe to each other.
But I would like also to know how much we spent in each month (because we both have a top budget we need to control).
I tried to use IF functions relating E8, E9, E... cells to the column A, but I had no success.
I have a template whereby it show agent hourly performance. I unable to use vlookup formula because duplicate id with different interval. If I select id 1977 it will auto update agent performance it the table according to the interval.
View 9 Replies View RelatedIs it possible to have Excel calculate using a fill down formula the number of days between a reoccuring value in a column? For example, I have column A formated as a DATE column containing every Wednesday and Saturday of the week for all of 2006. Column B has the value 21 in day intervals of 3, 5, 7, and 6 days. Currently, I'm counting the days (or ROWS) in between each time 21 appears manually. It would be cool if Excel can count the days in between each occurance of 21 using the DATE Data column on Column A.
View 5 Replies View RelatedLunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.
For accounting and payroll purposes, we need the totals to display in both hour and decimal format.
So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.
I have a file that sits open all the time, and performs some refresh functions every thirty minutes. I need the file to save a copy of the tab as a CSV file at a given time interval. The code below is almost there, just need to work with the time interval part. The way it should work is to open the csv, copy / paste the active sheet; then close the csv; leaving the original excel file open. I can run it, and it works, but the time interval is not triggering.
I can get the time interval to work by itself, and the save csv part to work by itself also; I need them to work together.
VB:
Sub test()
Application.OnTime Now + TimeSerial(0, 1, 0), "test"
Dim OutputFile As Workbook, InputFile As Workbook
Dim sDD As Worksheet
[Code].....
I want to create a room reservation sheet, based on the following:
December
Room 1
Room 2
08:00 am
09:00 am
10:00 am
Where the blank cells should be filled with a color (red, blue, anything), based on another sheet that tells me i.e that the room 1 is occupied from 09:00 to 10:00 and room 2 from 08:00 to 09:00 am.
I am currently trying to sum up values every day of the week. (salaries )
That means I have a sheet called Salary where I have the entire year mapped out pr. day. Like you see underneath:
Date
30/12/13 0
31/12/13 0
01/01/130
02/01/130
03/01/130
04/01/130
05/01/130
etc.
In another sheet called Overview, I have another column divided into weeks, like this:
Date
30/12/12 0
06/01/13 0
13/01/13 0
20/01/13 0
27/01/13 0
Now what I need to do is, take the 7 days in a week in the first sheet, and summarize it in the first week in the overview sheet. Now that is simple the first time, =SUM(Salary!C1:C7)
But how do I drag down and automize it so that the next row becomes =SUM(Salary!C8:C14) instead of =SUM(Salary!C2:C8)?
So basically I need to autofill with intervals of 7 (the 7 days in the week) on every row in my Overview sheet.
How to write a formula to calculate how many minutes an agent have been in Open Time by interval ...
Example if I have open time from 9:00-10:00 I need to calculate how many minutes were used from 9:00-9:30, from 9:30-10:00 and from 10:00-10:30
What formula can I use?
I need to get the total values within a criteria. Please see attached sample file.
View 5 Replies View Related