JIMMARSHALL6660
New Member
- Joined
- Mar 13, 2002
- Messages
- 12
I work as an administrator using excel for team registers which are then reported via pivot tables for timesheet purposes.
The problem I have is that several people work differing hours each day and on different days.
I therefore can't fix a deduction of say 1 hour as it deducts this from all hours worked irrespective of the number.
I need to be able to show if someone worked part of a day (3:30)and pay them for those hours and if more than 4 deduct 1 hour for lunch.Also required are the "H","A","S","U","OFF" AND "L"
A copy of the file and the formula used is attached.
Please help.
09:00 13:30 17:00 16:55 03:25 1
09:00 09:00 17:00 12:30 03:30 1
09:00 09:10 17:00 16:50 07:40 1
09:00 09:00 17:00 17:00 H H
09:00 09:05 17:00 16:55 07:50 1
09:00 09:00 17:00 16:50 07:50 1
09:00 09:00 17:00 17:00 08:00 1
=IF(J9=1,H9-F9,IF(J9="OFF","OFF",IF(J9="U","U",IF(J9="A","A",IF(J9="S","S",IF(J9="H","H",IF(J9="T","T",IF(J9="L","L"))))))))
The problem I have is that several people work differing hours each day and on different days.
I therefore can't fix a deduction of say 1 hour as it deducts this from all hours worked irrespective of the number.
I need to be able to show if someone worked part of a day (3:30)and pay them for those hours and if more than 4 deduct 1 hour for lunch.Also required are the "H","A","S","U","OFF" AND "L"
A copy of the file and the formula used is attached.
Please help.
09:00 13:30 17:00 16:55 03:25 1
09:00 09:00 17:00 12:30 03:30 1
09:00 09:10 17:00 16:50 07:40 1
09:00 09:00 17:00 17:00 H H
09:00 09:05 17:00 16:55 07:50 1
09:00 09:00 17:00 16:50 07:50 1
09:00 09:00 17:00 17:00 08:00 1
=IF(J9=1,H9-F9,IF(J9="OFF","OFF",IF(J9="U","U",IF(J9="A","A",IF(J9="S","S",IF(J9="H","H",IF(J9="T","T",IF(J9="L","L"))))))))