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 have attached a sheet where I have 4 graphs on one sheet. I prefer this to an overlay. I would like to add a 5th graph but it would make all the the graphs too small. Is there a way to make the graph background longer. For instance, extend the length of this so that I can put multiple graphs on it and then just scroll from top to bottom to review them? Also, how do I copy and paste these individual graphs into a newly created graph. copy/paste doesn't work on these?
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
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 am creating a spreadsheet system for storing school grades, and I have a sheet on my system which shows graphs of the students data, and on this sheet there are rather a lot of graphs, and its sort of a visual overload to see so many graphs on one sheet. Im just wondering if there is any thing which I could insert which would give the person using the system a choice of which class's graph they want to show.
I have sort of a weird request. I've got a chart with data on a patient with a sleep disorder and was hoping to create some charts based on the information I've gathered. For example, I've got a column with the amount of time before she falls asleep and another column with a type of food listed. I want to see if there's any correlation between these two variables. What would be the best type of graph to do this, and what formula would I use?
I am making an excel program that performs a series of complex calculations then produces several charts of the output. I have the output in a table of a known lenght and width. The problem comes when attempting to create the graphs, I first delete all the old graphs and make new ones. Then I try to add the series to the graphs. I want the graph to be one column vs another column but I have only been able to get it to graph a column vs 1, 2, 3, ect.
how to set the x-axis source data for a given series.
I have two seperate graphs in two different workbooks. Both graph's are identical in the setup, they have the same X axis and Y axis value. They are identical in every way except for the varying in percentages. This graph was originally setup in that it would calculate the results for a period of a week at a time. Is there a way to merge the two graphs thereby showing the results for a two week period?
I'm trying to make alot of graphs(400) and I'm not really sure where to start but here with what I've got so far.
Sub macro2() Dim x As Long, t As Long, y As Long, z As Long y = 3 z = 5 x = 4 While x < 1000 With Worksheets("cabernet (2)") t = Cells(x, 1).Value Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("By:Hz"), PlotBy:=xlRows ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)" ActiveWindow.Visible = False ActiveChart.HasTitle = True ActiveChart.charttitle.Text = " " & t Windows("complete Favorite Genes.xls").Activate y = y + 3 z = z + 3 x = x + 4 End With Wend End Sub
I don't really know if putting a variable directly into a range works, so far it doesn't.
As you can tell I need a line graph of the range B3:H5, then the next is the those same columns but 3 rows down, so the next graph is at B6:H8. I'd also like to through in a title here, but I may just be playing with fire. The title is located in column A starting at 4 and corrosponds with each graph, so it too goes is increments of 3, i.e., the first title is at 4 the second at 7 the third at 10. Somebody help me out this isn't working very well at all.
I currently have a 70 page spreadsheet with similar data that needs representing in pie chart format. All the column headers are the same and I only need 2 columns (A and T).
There are however different numbers of rows. Is it possible to write a macro to detect the number of rows before it draws the relevant pie chart (I've managed the pie chart bit!!)
See attached example, same concept but cant post real data.
