OT Tracking system for multiple employees

[Mikelowrey]

Good morning,

I would like to create a 2 spreadsheets that would track the overtime on over 50 employees, Cells would constitute of

DATE LASTNAME FIRSTNAME TOUR WORKED AMOUNT OF TIME REASON SUPAPPROVAL LOG # for the OT in time and

DATE LASTNAME FIRSTNAME TOUR WORKED AMOUNT OF TIME CASH REASON SUPAPPROVAL LOG # for the OT in cash

Now, in this I would like to be able to pull weekly, monthly, 28 days, quarterly and yearly reports, now my REAL question is, is this feasible in excel or is it better in access? I have no knowledge of access but I guess I could try if is more easier.

Thank you in advance.

 

[Comfy]

It is feasible yes, but I would suggest keeping it to one sheet.

So you would have:

Excel 2010
	A	B	C	D	E	F	G	H	I
3	Date	LastName	FirstName	Tour Worked	Amount of Time	Hourly Rate	Reason	SupaApproval	Log #
Sheet1

There's no need to duplicate the info.

 

[Mikelowrey]

It is feasible yes, but I would suggest keeping it to one sheet.

So you would have:

Excel 2010

	A	B	C	D	E	F	G	H	I
3	Date	LastName	FirstName	Tour Worked	Amount of Time	Hourly Rate	Reason	SupaApproval	Log #

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

There's no need to duplicate the info.

Comfy,

Thanks for the quick reply, I was thinking the same, all that info in just one sheet, and then have others sheets run the numbers i guess?

I did state the ways I woul dlike to run the reports, plus the following: by individuals, earnings, hours worked, and like a Query type thing. is this possible again? thanks!

 

[Comfy]

Yep, you can setup several Pivot Tables on different sheets to show what ever info you want.

Here are two very quick examples:

Source Data

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Date	LastName	FirstName	Tour Worked	Amount of Time	Hourly Rate	Total Pay	Reason	SupaApproval	Log #
2	01/01/2014	A	A	Tour 1	2	7.5	15	Staff Shortage	T Wilkins	1
3	02/01/2014	B	B	Tour 1	5	10	50	Staff Shortage	T Wilkins	2
4	03/01/2014	C	A	Tour 1	3	7.5	22.5	Staff Shortage	T Wilkins	3
5	04/01/2014	D	B	Tour 1	6	5	30	Staff Shortage	T Wilkins	4
6	05/01/2014	A	A	Tour 1	4	7.5	30	Staff Shortage	T Wilkins	5
7	06/01/2014	B	B	Tour 1	8	10	80	Staff Shortage	T Wilkins	6
8	07/01/2014	C	A	Tour 1	5	7.5	37.5	Staff Shortage	T Wilkins	7
9	08/01/2014	D	B	Tour 1	2	5	10	Staff Shortage	T Wilkins	8
10	09/01/2014	A	A	Tour 2	3	7.5	22.5	Staff Shortage	T Wilkins	9
11	10/01/2014	B	B	Tour 2	6	10	60	Staff Shortage	T Wilkins	10
12	11/01/2014	C	A	Tour 2	4	7.5	30	Staff Shortage	J Doe	11
13	12/01/2014	D	B	Tour 2	1	5	5	Staff Shortage	J Doe	12
14	13/01/2014	A	A	Tour 2	5	7.5	37.5	Staff Shortage	J Doe	13
15	01/02/2014	B	B	Tour 2	8	10	80	Staff Shortage	J Doe	14
16	02/02/2014	C	A	Tour 2	4	7.5	30	Staff Shortage	J Doe	15
17	03/02/2014	D	B	Tour 2	5	5	25	Staff Shortage	J Doe	16
18	04/02/2014	A	A	Tour 2	2	7.5	15	Staff Shortage	J Doe	17
19	05/02/2014	B	B	Tour 2	2	10	20	Staff Shortage	J Doe	18
Sheet1

Summary by Employee and Date

Excel 2010
	A	B	C	D	E
2
3		Column Labels
4		2014
5		Jan		Feb
6	Row Labels	Hours Worked	Total Owed	Hours Worked	Total Owed
7	A	14	105	2	15
8	B	19	190	10	100
9	C	12	90	4	30
10	D	9	45	5	25
11	Grand Total	54	430	21	170
12
Sheet5

Summarised by Tour and Hourly Rate

Excel 2010
	A	B	C
2
3	Tour and Hourly Rate	Hours Worked	Total Owed
4	Tour 1	35	275
5	5	8	40
6	7.5	14	105
7	10	13	130
8	Tour 2	40	325
9	5	6	30
10	7.5	18	135
11	10	16	160
12	Grand Total	75	600
13
Sheet4