I have a data which has a start time, end time, Stay Time(End-Start) and # of subjects. I am trying to create a graph, line, bar or histogram which shows how many subjects were there at a specific time. So the horizontal axis would have time from 00:00 to 24:00 and vertical axis would have the total # of subjects or the %.
I am attempting to create a datasheet to track the actual time an agent takes a break or lunch. Here is an example of the data that I am using.
name Date Lunch Sch Brk1 Sch Brk 2 Actual Time BrandonHolt8/19/201411:10 8:30 1:20 8:24:20 Break BrandonHolt8/19/201411:10 8:30 1:20 11:42:34 Break BrandonHolt8/19/201411:10 8:30 1:20 12:57:46 Lunch
Because I have two break times, possibility of more depending on the agent, how do i create a formula that will look at this data, take the schedule time for say break one and only apply that against the time that is more in line with the closest actual time?
I'm currently working on a rota for which I would like the worksheet to automatically tell me how many staff members I have beginning their shift before 9am and those finishing after 7pm. I currently have this working via a very crude set of IF statements for each staff member for each day of the week, returning 1 if true and 0 if false. Then I have a sum statement at the bottom of each day. Is there a much tidier and simpler way for me to calculate this?
I've attached the worksheet. A quick note is in Q52.
I am trying to create a time sheet where there will be a start time - end time - number of hours (difference between the 2) - pay rate - total pay, but am getting strange results!
I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :- Duration = (End Date,End Time) - (Start Date,Start Time)
I need to find out the total movement in start times per week for the employees.
I have used MIN and MAX to find the earliest and latest start times, and then a subtraction to find the difference.
This works brilliantly until my employees who start around midnight.
If they have started before and after midnight, it takes midnight as the earliest time and 23:00 as the latest, giving a difference of 23 hours instead of 1 hour.
i.e.
Start Times 23:00, 23:15, 00:15, 00:30
Range of Movement 00:30 - 23:00 = 01:30
I've tried, =$B$9+($C$4>$B$9)-$C$4 but this does not work when the MIN reads 00:00.
It's been several years sine I had to look at calculating amount of time worked. Can you please look at this old spreadsheet of mine and verify that the formula is correct?
It appears to be ok to me, but I don't want any errors when it comes to paying my employees
I am trying to come up with a formula that calculates total time someone has worked in a day. The scenario is an individual will work at a home and start working with an individual. Their start/end times look like this in a pivot:
Min Start Max Start Min End Max End Location A+Counselor A 8:56 AM4:01 PM 1:11 PM 7:00 PM Location A+Counselor B 12:00 AM 8:00 PM 6:00 AM 11:59 PM Location B:Counselor C 7:00 AM 12:00 PM 2:00 PM 4:00 PM Location C+Counselor D 8:00 AM 8:00 AM 4:00 PM 4:00 PM
Some people work split shifts while others work a straight shift. The formula I created was this:
=IF(OR(B9=C9,E9=D9,D9=C9),E9-B9,IF(D9>C9,((E9-D9)+(C9-B9)),IF(C9>D9,((D9-B9)+E9-C9),"New Formula Needed")))*24
(I use a pivot table to show max min for start and end times)
This works great except for the individuals that have multiple punches during the same time frame. The one scenario I am having trouble solving for is when someone punches in more than once during their shift displaying. This occurs when a counselor starts a shift working with one person but then adds another person mid shift. An example of this could be:
Location A+Counselor E Min Start Max Start Min End Max End Consumer 1 1:00 PM 1:00 PM 8:30 PM 8:30 PM Total Time: 7.5 Consumer 2 12:00 PM 12:00 PM 2:35 PM 2:35 Pm Total Time: 2.6
Pivot says that they worked a total of 10.1 because it is grabbing the max and mins and calculating. The actual total time worked is 8.5 hours in reality.
The raw data comes in like so:
Location Counselor Consumer Start Time End Time A A A 1:00 PM 8:30 PM A A B 12:00 PM 2:35 PM A A C 12:00 PM 5:00 PM
I want the start and end times of the shift to turn black or red depending on whether the adjacent cell says "off" of "Hol" respectively. I have this working except for when I actually enter smething into these cell ie a shift, the cell turns black.
Can I calculate the number of working hours between these two using a formula? So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00 H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
i have a report that needs to be filled out with total purchases daily that keeps a running total. So each day i have a column with a new figure. Looks like this:
Date Purcahses Total 30/01/09 10 10 31/01/09 10 20 01/02/09 10 30 02/02/09 10 40 etc
On the 20th of each month, i want to create a macro that wipes out the historical data prior to 1st of that month. i.e. on Feb 20, i want to lose all the january rows.
I download players names from the internet for a football program I have and then have a formula that separates their names from their numbers. The problem is that in the cell with the players name their is always a space before the name starts. How can I get rid of this space?
The current text I download comes from the internet as follows:
2 Tom Bellchambers 4 Jobe Watson 5 Brent Stanton 7 Leroy Jetta 9 Brendon Goddard 11 David Zaharakis 12 Stewart Crameri 15 Courtenay Dempsey
These are all in column A I have the following formula in column D:
The formula separates the names from the numbers but it leaves a space before the name starts such that in column D the first name woud be appear as:
Tom Bellchambers (or - ""Tom""Bellchambers) Jobe Watson
And so on. My computer tries to match up these names in another sheet but because there is a space before the name begins it doesn't register. I got the formula above from one of you geniuses a while back and have NFI what it really means but alter it slightly so it gets rid of that space at the start of the name.
I'm using this formula (in Cells D4:D10) to tabulate hours being spent on various testing activity categories (N,O,R,E,U,A,D). Is there a method I can use to permit me to change the range of cells being tabulated using Named Ranges or some sort of substitution?
= SUMIF($C$15:$C$89,C4,OFFSET($C$15:$C$89,0,4))
for example I would like to use the value I present in cell A2 as the starting point (for the range) & B2 as the end point (for the range) of cells being evaluated. In other words the improved formula would look like this.
=SUMIF(A2:B2,C4,OFFSET(A2:B2,0,4)) -or something like that-
I have a table where I assigned Months (Jan-Dec), Current Month YTD (e.g. Nov YTD) and Dec YTD in the column field. Company Name and Year in rows.
For a particular company for example, Co.AAA. The Start Date is Oct 1, 2009 and the contract will end on Sep 30, 2014.
If my current reporting month is Nov 2013, From the table, I need to get the sum up the data that correspond from this dates inclusive in start and end date of Co.AAA.
Inclusive Dates are: Dec YTD 2013 Dec YTD 2012 Dec YTD 2011 Dec YTD 2010 Dec 2009 Nov 2009 Oct 2009
If this is not possible, what other solution I can use to get the correct number?
I have $ 3.00 in cell G9 and have a percentage of 40% in another cell B9 (I would like to be able to change the 40% in this cell if I have to). I would then like to have the answer ($3.00 - 40%) $ 1.80 posted in a third cell below G10 What would be the formula for this?
I have an Excel workbook with two sheets "DataSheet" and "Actual Peaks", "DataSheet" contains a column with dates and a column with values. "Actual Peaks" has a bunch of dates listed in a column. For each date in 'Actual Peaks', another column goes back to the "DataSheet" finds the date, then starts adding values until a specified sum is reached, once the sum is reached, it returns the date at which the sum was reached. However, I have found that sometimes it is off by a day or two.
See the attached workbook for a much clearer example : Excel_forum help 7-23-14 DD validation.xlsx‎
I have data over the last 170 days. Each entry has a time stamp. I would like to count how many entries there are between 7:45 am and 2:45pm. A total count across all 170 days is needed, but it would be great if I could break that down by calendar day (i do have that as a column).
Here is a sample of the data:Friday11/15/1307:08AM
Is it possible to determine how many times a formula will be used? Here’s my problem.
I have items that are bought, and then altered. So I need a cell to determine how any times my ‘IF’ formula is used. My current formula is:
Formula entered into D3.
=IF(D2>0,D2,””)
Row 2 is the amount and Row 1 are dates.
If I drag the formula to the right, E2 will be equal to 0 so will enter 0. But I need it to say what ever is in D2 3 times. (As this particular item takes 3 weeks to do) So D2 & E2 & F3 will all have whatever was entered in D2.
The attached spreadsheet is a sample of a master sheet I need to maintain. Keep in mind that the spreadsheet has hundreds of names on it and 10 sheets in the workbook. What name appears most in the "person who called" column, then I need to know out of all the people who called, what percentage of calls he made....
I have to do this on each sheet, so if it is possible to have it work for all the sheets.