If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time performance Chart
Greetings. (help)
I need to develop a chart that graphically depicts an employees start time and end time each day. This chart would be a bar chart. The bar chart would have days of the month along the bottom, and the side would start at 00:00 at the bottom and end at 24:00 at the top. I need to be able to enter an employees start time and end time and have it charted along the bar. So if the employee started at 07:00 and ended work at 18:00, the chart would show a colored bar that corresponds to the start time and the end time. Also, if an employee came back to work the chart would reflect a second start time and end time. I would like to have the chart broken down to 15 minute increments. To add a little more info, each employee maintains daily log books, and I'm trying to graphically demonstrate possible fraud by entering the time claimed by the employee, then entering the time recorded by a time accounting system. I need to have both "bars" side by side for each work day. On bar would be what the employee said he worked, and the other bar would be the actual time recorded. I did all of this work three years ago in quatro pro, and now I can't find the files, and I'm having a hard time ramping back up for this task. I appreciate any suggestions. THANKS! |
#2
|
|||
|
|||
Time performance Chart
What you're making is a stacked column chart, like a vertically oriented
Gantt chart. Here's how I'd set it up. Put the days in column A, starting in A2. Put "In", "Out", "In", "Out" into G1:J1 (yes, leave a bunch of empty columns). Enter the In and Out times in the appropriate rows below these labels. This setup allows for a single break. In cell B2, enter the formula =G2. In cell C2 enter =H2-G2. Select the cell and drag the bottom right corner (the tiny black square on the border) to the right to fill C2:E2. Select C2:F2, and drag the little square down to fill as many rows as needed. Put arbitrary labels in B2:E2; if you want a blank, click in the cell and press the space key. Select A1:Ewhatever, and build a stacked column chart. Format the second and fourth series to show when the employee was diligently working, and format the first and third to indicate otherwise (double click on the series, select None for Border and Area). The 15 minute increments are up to the recording and data entry your employees follow. To format the Y axis, you can enter times into the axis scale parameter boxes. Use 6:00 for Min to signify 6 am, use 22:00 for Max to signify 10 pm, use 0:15 for Major to signify a 15 minute tick spacing, or use 1:00 for Major and 0:15 for Minor. To set up the employee's own log, I'd use line-type series, because side-by-side stacked columns can be a bit more complicated. Put that data, the raw times, into columns K:N. Right click on the chart, choose Source Data from the pop up menu, click on the series tab, then click Add and select K2:Krow for the values for the employee's start time. Right click on this series, choose Chart Type from the pop up menu, and choose a Line chart. Repeat the Add process for new series with data in columns L through N. They should be added as additional Line series; if not, change them as before. This seems like a long procedure, but I think it took me longer to write about doing it than doing it would have taken. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Bull Splat wrote: Greetings. (help) I need to develop a chart that graphically depicts an employees start time and end time each day. This chart would be a bar chart. The bar chart would have days of the month along the bottom, and the side would start at 00:00 at the bottom and end at 24:00 at the top. I need to be able to enter an employees start time and end time and have it charted along the bar. So if the employee started at 07:00 and ended work at 18:00, the chart would show a colored bar that corresponds to the start time and the end time. Also, if an employee came back to work the chart would reflect a second start time and end time. I would like to have the chart broken down to 15 minute increments. To add a little more info, each employee maintains daily log books, and I'm trying to graphically demonstrate possible fraud by entering the time claimed by the employee, then entering the time recorded by a time accounting system. I need to have both "bars" side by side for each work day. On bar would be what the employee said he worked, and the other bar would be the actual time recorded. I did all of this work three years ago in quatro pro, and now I can't find the files, and I'm having a hard time ramping back up for this task. I appreciate any suggestions. THANKS! |
#3
|
|||
|
|||
Time performance Chart
Thanks Jon, I've printed out your suggestions, and I'm building the
file now. One other quick question, is there any way to enter time in the military format ( 1200, 1300, 1415, etc) with out having to stop and enter the colon between the hour and minute integers? Having to enter the colon really slows down the data entry portion. I realize that each enter would have to be entered as a four digit entry, but that's OK, if we could find a work around to skip entering the colon every time. Thanks for you help. Jon Peltier wrote in message ... |
#4
|
|||
|
|||
Time performance Chart
Suppose you want to enter times in column B, starting in B3. In C3,
enter this formula, and fill it downward as far as you need: =IF(LEN(B3)=0,NA(),TIMEVALUE(LEFT(B3,LEN(B3)-2)&":"&RIGHT(B3,2))) If B3 is blank, C3 has #N/A, which is going to be ignored by the chart. When a military time is entered, the last two digits are placed after the colon in C3, and any preceding digits are place before the colon, then this is evaluated as a time. Format column C as a time, using the first number format in the Time category (13:30). The numbers in column B are just dummies, while those in column C are the ones you'll use in your charts. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Bull Splat wrote: Thanks Jon, I've printed out your suggestions, and I'm building the file now. One other quick question, is there any way to enter time in the military format ( 1200, 1300, 1415, etc) with out having to stop and enter the colon between the hour and minute integers? Having to enter the colon really slows down the data entry portion. I realize that each enter would have to be entered as a four digit entry, but that's OK, if we could find a work around to skip entering the colon every time. Thanks for you help. Jon Peltier wrote in message ... |
#5
|
|||
|
|||
Time performance Chart
Well, all of your suggestions have been plugged in, and it's starting
to look really great. Thanks for the tip on entering military time. I've been reading in several news groups about how to create side by side stacked bar charts. I have a solo stacked bar chart now working that depicts the time that the record keeping system recorded. That only took 12 hours of trial and error. I'm stuck trying to figure out how to plug into the graph, the data from the employee's log books, and I need this data to be displayed next to the time accounting stacked bar. I created a second data table, just like the one you suggested, but I'm stuck trying to figure out how to get the employees log book hours on a second Y axis. I've tried several different combos, and the chart always goes crazy. I read online about "staggering" the data, but I can't grasp why this would make any difference. My brain is about flatline now, and I need to walk away from this for tonight. Thanks for your help ;-) Jon Peltier wrote in message ... |
#6
|
|||
|
|||
Time performance Chart
Put the code below in a worksheet module. Change the cMilitaryTimeRange
constant to reflect the range of cells that you want converted automatically. Then, if text is entered, only legitimate 4 character times will be converted to a time field. For any other data (i.e., numeric) enter the time as 1, 2, 3, or 4 digits. 1 or 2 digit entries will be interpreted as minutes only. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const cMilitaryTimeRange As String = "C:C" If Intersect(Range(cMilitaryTimeRange), Target) Is Nothing Then Exit Sub Dim aCell As Range For Each aCell In Target With aCell If .HasFormula Or .HasArray Or IsDate(.Value) _ Or Application.WorksheetFunction.IsError(.Value) Then ElseIf Application.WorksheetFunction.IsNumber(.Value) Then If .Value = 1 And .Value \ 100 24 And .Value Mod 100 = 59 Then On Error Resume Next Application.EnableEvents = False .Value = TimeSerial(.Value \ 100, .Value Mod 100, 0) Application.EnableEvents = True On Error GoTo 0 End If ElseIf Len(.Value) = 4 Then If Left(.Value, 2) = "00" And Left(.Value, 2) "24" _ And Right(.Value, 2) = "00" And Right(.Value, 2) = "59" Then On Error Resume Next Application.EnableEvents = False .Value = TimeSerial(Left(.Value, 2), Right(.Value, 2), 0) Application.EnableEvents = True On Error GoTo 0 End If End If End With Next aCell End Sub -- Regards, Tushar Mehta MS MVP Excel 2000-2003 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article , says... Thanks Jon, I've printed out your suggestions, and I'm building the file now. One other quick question, is there any way to enter time in the military format ( 1200, 1300, 1415, etc) with out having to stop and enter the colon between the hour and minute integers? Having to enter the colon really slows down the data entry portion. I realize that each enter would have to be entered as a four digit entry, but that's OK, if we could find a work around to skip entering the colon every time. Thanks for you help. Jon Peltier wrote in message ... |
#7
|
|||
|
|||
Time performance Chart
THANKS! I'll plug this in today. This routine should really speed up data entry.
|
Thread Tools | |
Display Modes | |
|
|