#1   Report Post  
Posted to microsoft.public.excel.misc
GettingaHeadache
 
Posts: n/a
Default Time Sheet

I am trying to make a time sheet for everyone to be able to use in the
office. I want it to where when the total in Regular working hours reaches
37.5 it will put the excess working hours into another cell called of course
Overtime. My dilema is not only this it is also that there are 2 colums of
overtime. There is overtime for 37.5 hrs to 40 and then there is a column for
40+ hrs. Im so puzzled and confused I dont know if this is even going to be
possible or how to even go about setting it up in a way that it WILL work. If
anyone can please help me that would be so wonderful! If you want to see the
document I have put together so far I can email it to you.

Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
Cutter
 
Posts: n/a
Default Time Sheet


Using A1 as the cell containing the total hours worked:

For the first part of the OT use:
=IF(A137.5,A1-37.5,"")

For the second part of the OT use:
=IF(A140,A1-40,"")


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=527581

  #3   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Time Sheet

=MIN(MAX(num_hours-37.5,0),2.5)

for hours between 37.5 and 40

=MAX(num_hours-40,0)

for hours over 40

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
I am trying to make a time sheet for everyone to be able to use in the
office. I want it to where when the total in Regular working hours reaches
37.5 it will put the excess working hours into another cell called of

course
Overtime. My dilema is not only this it is also that there are 2 colums of
overtime. There is overtime for 37.5 hrs to 40 and then there is a column

for
40+ hrs. Im so puzzled and confused I dont know if this is even going to

be
possible or how to even go about setting it up in a way that it WILL work.

If
anyone can please help me that would be so wonderful! If you want to see

the
document I have put together so far I can email it to you.

Thanks!



  #4   Report Post  
Posted to microsoft.public.excel.misc
GettingaHeadache
 
Posts: n/a
Default Time Sheet

I'm not quite sure what to do here, I am new to the advanced formulas in
excel. I dont want the employees to have to enter anything but their time. So
in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in "num_hours"?

Ok lets say that their regular hours total 52.50 which is obviously 15 hrs
of overtime but there is going to be 37.5-40 hrs of overtime which is only
2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to where
the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs
will hit cell "a2" automatically? I am not sure if I am explaining this clear
enough but I dont know how else to say it. Please Help!!!

"Bob Phillips" wrote:

=MIN(MAX(num_hours-37.5,0),2.5)

for hours between 37.5 and 40

=MAX(num_hours-40,0)

for hours over 40

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
I am trying to make a time sheet for everyone to be able to use in the
office. I want it to where when the total in Regular working hours reaches
37.5 it will put the excess working hours into another cell called of

course
Overtime. My dilema is not only this it is also that there are 2 colums of
overtime. There is overtime for 37.5 hrs to 40 and then there is a column

for
40+ hrs. Im so puzzled and confused I dont know if this is even going to

be
possible or how to even go about setting it up in a way that it WILL work.

If
anyone can please help me that would be so wonderful! If you want to see

the
document I have put together so far I can email it to you.

Thanks!




  #5   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Time Sheet

num_hours refers to the number of hours worked, 52.5 in your example.

Assuming that the hours are in a cell, let's say A1, you just add those
formulae to A2 and A3

=MIN(MAX(A1-37.5,0),2.5)

=MAX(A1-40,0)

When your use puts their time in A1, the others autonmatically calculate

HTH

MigraineReliever


"GettingaHeadache" wrote in
message ...
I'm not quite sure what to do here, I am new to the advanced formulas in
excel. I dont want the employees to have to enter anything but their time.

So
in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in

"num_hours"?

Ok lets say that their regular hours total 52.50 which is obviously 15 hrs
of overtime but there is going to be 37.5-40 hrs of overtime which is only
2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to

where
the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs
will hit cell "a2" automatically? I am not sure if I am explaining this

clear
enough but I dont know how else to say it. Please Help!!!

"Bob Phillips" wrote:

=MIN(MAX(num_hours-37.5,0),2.5)

for hours between 37.5 and 40

=MAX(num_hours-40,0)

for hours over 40

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
I am trying to make a time sheet for everyone to be able to use in the
office. I want it to where when the total in Regular working hours

reaches
37.5 it will put the excess working hours into another cell called of

course
Overtime. My dilema is not only this it is also that there are 2

colums of
overtime. There is overtime for 37.5 hrs to 40 and then there is a

column
for
40+ hrs. Im so puzzled and confused I dont know if this is even going

to
be
possible or how to even go about setting it up in a way that it WILL

work.
If
anyone can please help me that would be so wonderful! If you want to

see
the
document I have put together so far I can email it to you.

Thanks!








  #6   Report Post  
Posted to microsoft.public.excel.misc
GettingaHeadache
 
Posts: n/a
Default Time Sheet

Ahh! I got it!!! Now my new task is how to have it calculate their leave and
vaction time into their hours.. Any suggestions?! You are a ton of help!
Thanks!


"Bob Phillips" wrote:

num_hours refers to the number of hours worked, 52.5 in your example.

Assuming that the hours are in a cell, let's say A1, you just add those
formulae to A2 and A3

=MIN(MAX(A1-37.5,0),2.5)

=MAX(A1-40,0)

When your use puts their time in A1, the others autonmatically calculate

HTH

MigraineReliever


"GettingaHeadache" wrote in
message ...
I'm not quite sure what to do here, I am new to the advanced formulas in
excel. I dont want the employees to have to enter anything but their time.

So
in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in

"num_hours"?

Ok lets say that their regular hours total 52.50 which is obviously 15 hrs
of overtime but there is going to be 37.5-40 hrs of overtime which is only
2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to

where
the 2.5hrs of overtime will hit the cell "a1" for example and the 12.5hrs
will hit cell "a2" automatically? I am not sure if I am explaining this

clear
enough but I dont know how else to say it. Please Help!!!

"Bob Phillips" wrote:

=MIN(MAX(num_hours-37.5,0),2.5)

for hours between 37.5 and 40

=MAX(num_hours-40,0)

for hours over 40

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
I am trying to make a time sheet for everyone to be able to use in the
office. I want it to where when the total in Regular working hours

reaches
37.5 it will put the excess working hours into another cell called of
course
Overtime. My dilema is not only this it is also that there are 2

colums of
overtime. There is overtime for 37.5 hrs to 40 and then there is a

column
for
40+ hrs. Im so puzzled and confused I dont know if this is even going

to
be
possible or how to even go about setting it up in a way that it WILL

work.
If
anyone can please help me that would be so wonderful! If you want to

see
the
document I have put together so far I can email it to you.

Thanks!






  #7   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Time Sheet

I would need a bit more detail to know how the leave is shown on the s/s.
What is the difference between leave and vacation time?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
Ahh! I got it!!! Now my new task is how to have it calculate their leave

and
vaction time into their hours.. Any suggestions?! You are a ton of help!
Thanks!


"Bob Phillips" wrote:

num_hours refers to the number of hours worked, 52.5 in your example.

Assuming that the hours are in a cell, let's say A1, you just add those
formulae to A2 and A3

=MIN(MAX(A1-37.5,0),2.5)

=MAX(A1-40,0)

When your use puts their time in A1, the others autonmatically calculate

HTH

MigraineReliever


"GettingaHeadache" wrote in
message ...
I'm not quite sure what to do here, I am new to the advanced formulas

in
excel. I dont want the employees to have to enter anything but their

time.
So
in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in

"num_hours"?

Ok lets say that their regular hours total 52.50 which is obviously 15

hrs
of overtime but there is going to be 37.5-40 hrs of overtime which is

only
2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to

where
the 2.5hrs of overtime will hit the cell "a1" for example and the

12.5hrs
will hit cell "a2" automatically? I am not sure if I am explaining

this
clear
enough but I dont know how else to say it. Please Help!!!

"Bob Phillips" wrote:

=MIN(MAX(num_hours-37.5,0),2.5)

for hours between 37.5 and 40

=MAX(num_hours-40,0)

for hours over 40

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache"

wrote in
message ...
I am trying to make a time sheet for everyone to be able to use in

the
office. I want it to where when the total in Regular working hours

reaches
37.5 it will put the excess working hours into another cell called

of
course
Overtime. My dilema is not only this it is also that there are 2

colums of
overtime. There is overtime for 37.5 hrs to 40 and then there is a

column
for
40+ hrs. Im so puzzled and confused I dont know if this is even

going
to
be
possible or how to even go about setting it up in a way that it

WILL
work.
If
anyone can please help me that would be so wonderful! If you want

to
see
the
document I have put together so far I can email it to you.

Thanks!








  #8   Report Post  
Posted to microsoft.public.excel.misc
GettingaHeadache
 
Posts: n/a
Default Time Sheet

Leave is when you are sick, have a doctors appointment, sick relative etc.
Vacation is when you go on vacation or take a day off to do personal business.
Right now I dont have a format for the leave or vacation time my boss just
informed me she wants me to work it in there somehow. The setup I have atm is
regular hrs, leave hrs, vacation hrs, worked hrs, OT 37.5-40, OT 40+ and
total hrs. What I eventually want is for worked hours to take out lunch hour,
vacation and leave time. The total hours would include the lunch hour,
vacation, and leave time so that way they know what they worked for that
whole day. Am i doing double duty on this? I dont want to confuse the payroll
with 2 totals plus OT totals let alone confuse the employees... If you want
to see the spreadsheet I can email it to you or you can contact me @
. Thanks!

"Bob Phillips" wrote:

I would need a bit more detail to know how the leave is shown on the s/s.
What is the difference between leave and vacation time?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
Ahh! I got it!!! Now my new task is how to have it calculate their leave

and
vaction time into their hours.. Any suggestions?! You are a ton of help!
Thanks!


"Bob Phillips" wrote:

num_hours refers to the number of hours worked, 52.5 in your example.

Assuming that the hours are in a cell, let's say A1, you just add those
formulae to A2 and A3

=MIN(MAX(A1-37.5,0),2.5)

=MAX(A1-40,0)

When your use puts their time in A1, the others autonmatically calculate

HTH

MigraineReliever


"GettingaHeadache" wrote in
message ...
I'm not quite sure what to do here, I am new to the advanced formulas

in
excel. I dont want the employees to have to enter anything but their

time.
So
in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in
"num_hours"?

Ok lets say that their regular hours total 52.50 which is obviously 15

hrs
of overtime but there is going to be 37.5-40 hrs of overtime which is

only
2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it to
where
the 2.5hrs of overtime will hit the cell "a1" for example and the

12.5hrs
will hit cell "a2" automatically? I am not sure if I am explaining

this
clear
enough but I dont know how else to say it. Please Help!!!

"Bob Phillips" wrote:

=MIN(MAX(num_hours-37.5,0),2.5)

for hours between 37.5 and 40

=MAX(num_hours-40,0)

for hours over 40

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache"

wrote in
message ...
I am trying to make a time sheet for everyone to be able to use in

the
office. I want it to where when the total in Regular working hours
reaches
37.5 it will put the excess working hours into another cell called

of
course
Overtime. My dilema is not only this it is also that there are 2
colums of
overtime. There is overtime for 37.5 hrs to 40 and then there is a
column
for
40+ hrs. Im so puzzled and confused I dont know if this is even

going
to
be
possible or how to even go about setting it up in a way that it

WILL
work.
If
anyone can please help me that would be so wonderful! If you want

to
see
the
document I have put together so far I can email it to you.

Thanks!









  #9   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Time Sheet

Well there is nothing difficult about what you want, but until you show the
layout there is not much more to say, as it would just be guessing?

Mail me the spreadsheet

bob (dot) phillips (at) tiscali (dot) co (dot) uk

do the obvious with the bits in brackets

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
Leave is when you are sick, have a doctors appointment, sick relative etc.
Vacation is when you go on vacation or take a day off to do personal

business.
Right now I dont have a format for the leave or vacation time my boss just
informed me she wants me to work it in there somehow. The setup I have atm

is
regular hrs, leave hrs, vacation hrs, worked hrs, OT 37.5-40, OT 40+ and
total hrs. What I eventually want is for worked hours to take out lunch

hour,
vacation and leave time. The total hours would include the lunch hour,
vacation, and leave time so that way they know what they worked for that
whole day. Am i doing double duty on this? I dont want to confuse the

payroll
with 2 totals plus OT totals let alone confuse the employees... If you

want
to see the spreadsheet I can email it to you or you can contact me @
. Thanks!

"Bob Phillips" wrote:

I would need a bit more detail to know how the leave is shown on the

s/s.
What is the difference between leave and vacation time?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache" wrote in
message ...
Ahh! I got it!!! Now my new task is how to have it calculate their

leave
and
vaction time into their hours.. Any suggestions?! You are a ton of

help!
Thanks!


"Bob Phillips" wrote:

num_hours refers to the number of hours worked, 52.5 in your

example.

Assuming that the hours are in a cell, let's say A1, you just add

those
formulae to A2 and A3

=MIN(MAX(A1-37.5,0),2.5)

=MAX(A1-40,0)

When your use puts their time in A1, the others autonmatically

calculate

HTH

MigraineReliever


"GettingaHeadache"

wrote in
message ...
I'm not quite sure what to do here, I am new to the advanced

formulas
in
excel. I dont want the employees to have to enter anything but

their
time.
So
in the formula =MIN(MAX(num_hours-37.5,0),2.5) what do I put in
"num_hours"?

Ok lets say that their regular hours total 52.50 which is

obviously 15
hrs
of overtime but there is going to be 37.5-40 hrs of overtime which

is
only
2.5hrs and then there is 40+ hrs which is 12.5hrs how do I make it

to
where
the 2.5hrs of overtime will hit the cell "a1" for example and the

12.5hrs
will hit cell "a2" automatically? I am not sure if I am explaining

this
clear
enough but I dont know how else to say it. Please Help!!!

"Bob Phillips" wrote:

=MIN(MAX(num_hours-37.5,0),2.5)

for hours between 37.5 and 40

=MAX(num_hours-40,0)

for hours over 40

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GettingaHeadache"

wrote in
message

...
I am trying to make a time sheet for everyone to be able to

use in
the
office. I want it to where when the total in Regular working

hours
reaches
37.5 it will put the excess working hours into another cell

called
of
course
Overtime. My dilema is not only this it is also that there are

2
colums of
overtime. There is overtime for 37.5 hrs to 40 and then there

is a
column
for
40+ hrs. Im so puzzled and confused I dont know if this is

even
going
to
be
possible or how to even go about setting it up in a way that

it
WILL
work.
If
anyone can please help me that would be so wonderful! If you

want
to
see
the
document I have put together so far I can email it to you.

Thanks!











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
time sheet drop down lists Steve Excel Discussion (Misc queries) 0 March 16th 06 10:45 PM
Time Sheet S.E. Excel Discussion (Misc queries) 2 October 14th 05 07:22 PM
Formula to deduct unpaid breaks in time sheet Rick Excel Discussion (Misc queries) 3 August 26th 05 11:53 PM
time sheet calculations emmcee Excel Worksheet Functions 7 August 20th 05 12:26 PM
Excel formula for a time sheet HRMSN Excel Worksheet Functions 1 August 10th 05 03:07 PM


All times are GMT +1. The time now is 11:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"