Excel Timesheet

Question

I have some data in the following format:

Salary
Code InTime

1690 09:03:00
1690 09:13:00
1690 09:07:00
1691 08:48:00
1691 08:52:00
1691 08:50:00
1691 08:54:00
1691 08:46:00
1691 09:28:00
1691 08:59:00
1691 08:53:00
1691 09:02:00
1693 08:57:00
1693 09:01:00
1693 08:54:00
1693 09:22:00
1693 09:16:00
1693 08:58:00
1693 09:03:00
1693 09:01:00
1693 09:02:00
1693 09:44:00
1885 09:28:00
1885 09:04:00
1885 09:20:00
1885 08:57:00

and so it goes for about 2000 lines I need to plot a line graph , that plots the intime for each salary code as a trend, with seperate lines for seperate salary codes. Is there an automated way to do this? Please note that the salary codes repeat for each day. The trend required is for each salary code, not row by row.

Answer 1

It can be done without programming, but there is one bit of information you need to add in order for this to make sense - the date or sequence number for each entry. Then you will be able to plot:

1. salary code: the series
2. date or sequence number: the x-axis
3. In Time: the value / y-axis

The easiest way is with the PivotChart Wizard:

• select the (now) 3 columns and choose "Data | PivotTable and PivotChart Report.."
• select PivotChart type
• click through the wizard to completion ... you should end up the raw chart page
• right-click chart are, choose "chart type" and change to "line" type
• from the "PivotTable Field List", choose:
  • date or sequence number - add to "Category Axis"
  • salary code - add to the series axis
  • in time - add to the data area
• By default, it will plot "Count of In Time": need to change this
  • right-click the "Count of In Time" field button widget
  • choose "Format PivotChart Field"
  • change to sum, average, max or min (anything that represents the actual data - it only applies to the one value so aggregate functions are fine)
• Now your chart lines should be OK, but the y-axis is just a meaningless value.
  • right-click the y-axis and choose "format axis"
  • go to the "Number" tab and change the format to "Time"

Voila! Lines for each salary code, plotting the "in time" trend day by day

Answer 2

I don't believe there is an automated way to do this but possibly you could generate a third column which holds the delta between the time and some arbitrary base time (say 12:00 am or 00:00). Then chart the deltas.

Answer 3

I don't think there is an easy way out..

the best I can do in 5 minutes is flip the columns, select and Create an XY Scatter plot.

It still won't give you different dot colors for each employee or individual lines