Time performance Chart

  • Thread starter Thread starter Bull Splat
  • Start date Start date
B

Bull Splat

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!
 
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:E<whatever>, 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:K<row> 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
 
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 <[email protected]> wrote in message news:<[email protected]>...
 
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
 
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 ;-)






 
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
 
Back
Top