Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
tb
 
Posts: n/a
Default Calculating a time sheet?

I currently have a time sheet set up with columns for my employees hours for
the day then total for the week, then I have a column that adds their wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by hand)
anything over 44 hours is overtime, is there a formula I can use to have for
my regular hours in 1 column x the regular wage, then another column for O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T. (B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb
  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to have
for
my regular hours in 1 column x the regular wage, then another column for
O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb



  #3   Report Post  
Roger Govier
 
Posts: n/a
Default

Biff
Won't that pay 44 hours even if 38 hours (for example) are worked?

perhaps
B15 =if(sum(B8:B14)44,44,sum(B8:B14))
and
B16 =if(sum(B8:B14)<=44,0,sum(B8:B14)-44))


--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their
wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to have
for
my regular hours in 1 column x the regular wage, then another column for
O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb





  #4   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Won't that pay 44 hours even if 38 hours (for example) are worked?


No

=MIN(44,SUM(B8:B14))

If the sum of hours in B8:B14 = 38

=MIN(44,38)

If the sum of hours in B8:B14 = 56

=MIN(44,56)

Biff

"Roger Govier" wrote in message
...
Biff
Won't that pay 44 hours even if 38 hours (for example) are worked?

perhaps
B15 =if(sum(B8:B14)44,44,sum(B8:B14))
and
B16 =if(sum(B8:B14)<=44,0,sum(B8:B14)-44))


--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their
wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to have
for
my regular hours in 1 column x the regular wage, then another column for
O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb







  #5   Report Post  
Roger Govier
 
Posts: n/a
Default

Hi Biff
Apologies. I was not reading that straight. My eyes saw MIN, my brain though
MAX. Too much wine with dinner last night!!!

--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

Won't that pay 44 hours even if 38 hours (for example) are worked?


No

=MIN(44,SUM(B8:B14))

If the sum of hours in B8:B14 = 38

=MIN(44,38)

If the sum of hours in B8:B14 = 56

=MIN(44,56)

Biff

"Roger Govier" wrote in message
...
Biff
Won't that pay 44 hours even if 38 hours (for example) are worked?

perhaps
B15 =if(sum(B8:B14)44,44,sum(B8:B14))
and
B16 =if(sum(B8:B14)<=44,0,sum(B8:B14)-44))


--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their
wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to
have for
my regular hours in 1 column x the regular wage, then another column
for O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb








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 claculation Frantic3d Excel Discussion (Misc queries) 4 July 1st 05 11:22 AM
Time stamp in Excel Sheet John M Excel Discussion (Misc queries) 3 June 28th 05 02:05 PM
I need a time sheet template that verifies time entered against sy Bob Powell Excel Discussion (Misc queries) 1 April 19th 05 03:11 PM
How do I set up a formula on a time sheet to calculate time in 1/. gschmid Excel Discussion (Misc queries) 2 January 18th 05 02:48 PM
calculating date time ranges Patrick Excel Worksheet Functions 2 November 11th 04 06:07 AM


All times are GMT +1. The time now is 04:55 PM.

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"