I receive pre-printed time sheets at work that have been manually filled in
by operatives, e.g Time of work: 9am-5pm. They will then have the hours
worked manually summed. I want to be able to check to make sure that these
are the correct hours by creating a formula to deal with this.
Later on I would like to create a formula that deals with over-time rates,
but I will deal with that later.
I'm a relative newcomer to Excel; Nested IF functions is about as the depth
of my knowledge.

Some details of the layout and what you want to achieve in specific terms
would help, it is far too generic at present.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DriverY" wrote in message
...
I receive pre-printed time sheets at work that have been manually filled
in
by operatives, e.g Time of work: 9am-5pm. They will then have the hours
worked manually summed. I want to be able to check to make sure that these
are the correct hours by creating a formula to deal with this.
Later on I would like to create a formula that deals with over-time rates,
but I will deal with that later.
I'm a relative newcomer to Excel; Nested IF functions is about as the
depth
of my knowledge.

hi driver
It doesn't need much complex functions.

just type the arrival time in one column, departure in the other,

in the next cell take difference, that is all
A B
C
1. 3:42 PM 5:42 PM 02:00:00
2. 3:42 PM 4:42 PM 01:00:00

copy this to an excel worksheet.
type in the column c row1 =b1-a1
column c row2 =b2-a2

select C column and go to FORMAT-CELLS-NUMBER-CUSTOM-
in the type box type the follwing

hh:mm:ss

now you will get the total time spent by each person.
Do you want to note the late comings??
like 9.30 office time and all came late by 10 minutes like that?


YOurs,
sajay

"sajay" wrote:

hi driver
It doesn't need much complex functions.

just type the arrival time in one column, departure in the other,

in the next cell take difference, that is all
A B
C
1. 3:42 PM 5:42 PM 02:00:00
2. 3:42 PM 4:42 PM 01:00:00

copy this to an excel worksheet.
type in the column c row1 =b1-a1
column c row2 =b2-a2

select C column and go to FORMAT-CELLS-NUMBER-CUSTOM-
in the type box type the follwing

hh:mm:ss

now you will get the total time spent by each person.
Do you want to note the late comings??
like 9.30 office time and all came late by 10 minutes like that?


YOurs,
sajay

Thanks for that Sayjay, it really was very helpful. Simple now I know, but
when you don't....In response to your question you have pre-empted me. I want
to know how to get calculate totals for operatives who work overtime.
E.g. Joe Bloggs works 8am till 4:30pm. He is paid 8 hours (he receives 1/2
hour unpaid lunch break). Suppose he works another hour until 5.30pm and is
paid at 1.5 times his rate. How do I set up a formula where Excel will be
able to differentiate between the normal hours worked, and the over-time
worked?

I appreciate your help

Kind regards

Driver

"Bob Phillips" wrote:

Some details of the layout and what you want to achieve in specific terms
would help, it is far too generic at present.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DriverY" wrote in message
...
I receive pre-printed time sheets at work that have been manually filled
in
by operatives, e.g Time of work: 9am-5pm. They will then have the hours
worked manually summed. I want to be able to check to make sure that these
are the correct hours by creating a formula to deal with this.
Later on I would like to create a formula that deals with over-time rates,
but I will deal with that later.
I'm a relative newcomer to Excel; Nested IF functions is about as the
depth
of my knowledge.

Yes you'll have to excuse the vagueness of my request, but thanks for taking
the time to look into it. I believe I have resolved the problems courtesey of
"Sajay" whereby I input the time an operative has worked e.g.
A1=8:00 B1=16:30 C1=(=B1-A1). I have then formatted the cell to custom
hh:mm:ss.
What I would like to know and I have asked Sajay the same question is what
function I need to input if an operative works an hour over time. For
instance in the example above let's say worker A worked 8:00am to 4:30pm with
half an hour unpaid lunch break. That leaves 8 hours of work at the worker's
normal rate.
How would I get Excel to differentiate between the 8 hours worked at normal
time and say 1 hour at 1.5*the rate i.e. working 8:00am-5:30pm?
Thanks

Regards


Driver

Sajay

Have you got any of my e-mails?

Tim/DriverY

l

"DriverY" wrote:

I receive pre-printed time sheets at work that have been manually filled in
by operatives, e.g Time of work: 9am-5pm. They will then have the hours
worked manually summed. I want to be able to check to make sure that these
are the correct hours by creating a formula to deal with this.
Later on I would like to create a formula that deals with over-time rates,
but I will deal with that later.
I'm a relative newcomer to Excel; Nested IF functions is about as the depth
of my knowledge.

Hello Bob

I have been trying to e-mail you but without any success. It says that your
domain name is not recognised.



Here's the time sheet I'm trying to create:



The standard hours of work are 8 hours per day and half an hour unpaid lunch
break



Operatives begin work at 8:00am and finish at 16:30 thus their 8 hour of
work. They are thus paid for 8 hours work.

However operatives rarely work a 40 hour week and so overtime kicks in.



For the following three hours they are paid at time and a half:

E.g.

8:00-16:30= 8 hours

8:00-19:30= 8 hours + 4.5 (3*1.5 been the time and a half component) =12.5
hours

Thereafter pay is double time

E.g.2

8:00-20:00= 8 hours +4.5 hours (3*1.5 hours) + 1 hour (0.5*2 been the double
time component) =13 hours



How can I create a formula whereby Excel will differentiate between the
different overtime rates once the 8 hour mark has been passed?



Currently my formula is set up like this, compliments of "Sajay":



D2=Time began E2= Time finished F3=Lunch break
G4=Hours worked

D3=8:00 E3=16:30
F3=00:30 =E3-D3-F3



G4 has been "Custom Formatted" to hh:mm





N.B Operatives may begin work at 7:00 working until 16:30. In this instance
their overtime rate would kick in at 15:30 and be paid time and a half for
the remaining hour.



Finally on the occasions where operatives work on a Saturday They are paid
at a rate of 1.5*hours worked for the first 5 hours and then double time
thereafter



On a Sunday it is straightforward 2*hours.



Your help would be greatly appreciated



Kind regards







Tim/ DriverY

Read my signature.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DriverY" wrote in message
...
Hello Bob

I have been trying to e-mail you but without any success. It says that
your
domain name is not recognised.



Here's the time sheet I'm trying to create:



The standard hours of work are 8 hours per day and half an hour unpaid
lunch
break



Operatives begin work at 8:00am and finish at 16:30 thus their 8 hour of
work. They are thus paid for 8 hours work.

However operatives rarely work a 40 hour week and so overtime kicks in.



For the following three hours they are paid at time and a half:

E.g.

8:00-16:30= 8 hours

8:00-19:30= 8 hours + 4.5 (3*1.5 been the time and a half component) =12.5
hours

Thereafter pay is double time

E.g.2

8:00-20:00= 8 hours +4.5 hours (3*1.5 hours) + 1 hour (0.5*2 been the
double
time component) =13 hours



How can I create a formula whereby Excel will differentiate between the
different overtime rates once the 8 hour mark has been passed?



Currently my formula is set up like this, compliments of "Sajay":



D2=Time began E2= Time finished F3=Lunch
break
G4=Hours worked

D3=8:00 E3=16:30
F3=00:30 =E3-D3-F3



G4 has been "Custom Formatted" to hh:mm





N.B Operatives may begin work at 7:00 working until 16:30. In this
instance
their overtime rate would kick in at 15:30 and be paid time and a half for
the remaining hour.



Finally on the occasions where operatives work on a Saturday They are paid
at a rate of 1.5*hours worked for the first 5 hours and then double time
thereafter



On a Sunday it is straightforward 2*hours.



Your help would be greatly appreciated



Kind regards







Tim/ DriverY