Time sheets

[DriverY]

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.

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[sajay]

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

[DriverY]

"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

[DriverY]

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

[DriverY]

Sajay

Have you got any of my e-mails?

Tim/DriverY

[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.

[DriverY]

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

[Bob Phillips]

Read my signature.

--

HTH

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


"DriverY" <[email protected]> wrote in message
news:[email protected]...
> 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
>