Here's a complicated problem. I can't change the format of the current
timesheet. The overtime is calculated by taking the total hours worked
and subracting 8. This is fine, but two things break it. 1) When less
than 8 hours are worked to compensate, and 2) when an entire shift is
overtime. These are two really big things that should have been fixed
when the timesheet was first made.

Anyways, I have been working on fixing the problem. So far, I have
created a way of correcting problem #1. I have a running total. If the
total is 0, then OT is displayed, else 0 is displayed. Actually
working overtime breaks this, too, though. Then you end up with both
the shifted OT displayed and the actual OT displayed.

A third problem that arose: It is a Bi-weekly timesheet and OT is
calculated weekly. How would I tell the spreadsheet was a week is? I
can't change Sheet1, so I am just planning on pointing to it in other
sheets. Thanks in advance.

matiez

Some sample data and existing formulae would help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Here's a complicated problem. I can't change the format of the current
timesheet. The overtime is calculated by taking the total hours worked
and subracting 8. This is fine, but two things break it. 1) When less
than 8 hours are worked to compensate, and 2) when an entire shift is
overtime. These are two really big things that should have been fixed
when the timesheet was first made.

Anyways, I have been working on fixing the problem. So far, I have
created a way of correcting problem #1. I have a running total. If the
total is 0, then OT is displayed, else 0 is displayed. Actually
working overtime breaks this, too, though. Then you end up with both
the shifted OT displayed and the actual OT displayed.

A third problem that arose: It is a Bi-weekly timesheet and OT is
calculated weekly. How would I tell the spreadsheet was a week is? I
can't change Sheet1, so I am just planning on pointing to it in other
sheets. Thanks in advance.

matiez

Start Time End Time Lunch Regular Hours Worked OverTime Total Hours
8:00 AM 4:30 PM 0.5 8 0 8
8:00 AM 5:30 PM 0.5 8 1 9
8:00 AM 5:30 PM 0.5 8 0 8
8:00 AM 3:30 PM 0.5 7 0 7

Reg hours=((C9-B9)*24)+H9-I9-L9 Where L9=OT, H9=Other, and I9=Lunch

OT=IF(M9-80,M9-8,0) where M9=Total Hours Paid. I have modified this
to: IF($M$22-800,(IF(M9-80,M9-8,0)),0) where M22=sum of total hours
paid.

Total hourse paid=((C9-B9)*24)+K9+H9-I9 Where K9=Leave Time (sick,
ect.), H9=Other, and I9=Lunch.

Brandon

Bob Phillips wrote:
Some sample data and existing formulae would help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Here's a complicated problem. I can't change the format of the current
timesheet. The overtime is calculated by taking the total hours worked
and subracting 8. This is fine, but two things break it. 1) When less
than 8 hours are worked to compensate, and 2) when an entire shift is
overtime. These are two really big things that should have been fixed
when the timesheet was first made.

Anyways, I have been working on fixing the problem. So far, I have
created a way of correcting problem #1. I have a running total. If the
total is 0, then OT is displayed, else 0 is displayed. Actually
working overtime breaks this, too, though. Then you end up with both
the shifted OT displayed and the actual OT displayed.

A third problem that arose: It is a Bi-weekly timesheet and OT is
calculated weekly. How would I tell the spreadsheet was a week is? I
can't change Sheet1, so I am just planning on pointing to it in other
sheets. Thanks in advance.

matiez

Haven't we been here before on this one?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Start Time End Time Lunch Regular Hours Worked OverTime Total Hours
8:00 AM 4:30 PM 0.5 8 0 8
8:00 AM 5:30 PM 0.5 8 1 9
8:00 AM 5:30 PM 0.5 8 0 8
8:00 AM 3:30 PM 0.5 7 0 7

Reg hours=((C9-B9)*24)+H9-I9-L9 Where L9=OT, H9=Other, and I9=Lunch

OT=IF(M9-80,M9-8,0) where M9=Total Hours Paid. I have modified this
to: IF($M$22-800,(IF(M9-80,M9-8,0)),0) where M22=sum of total hours
paid.

Total hourse paid=((C9-B9)*24)+K9+H9-I9 Where K9=Leave Time (sick,
ect.), H9=Other, and I9=Lunch.

Brandon

Bob Phillips wrote:
Some sample data and existing formulae would help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Here's a complicated problem. I can't change the format of the current
timesheet. The overtime is calculated by taking the total hours worked
and subracting 8. This is fine, but two things break it. 1) When less
than 8 hours are worked to compensate, and 2) when an entire shift is
overtime. These are two really big things that should have been fixed
when the timesheet was first made.

Anyways, I have been working on fixing the problem. So far, I have
created a way of correcting problem #1. I have a running total. If the
total is 0, then OT is displayed, else 0 is displayed. Actually
working overtime breaks this, too, though. Then you end up with both
the shifted OT displayed and the actual OT displayed.

A third problem that arose: It is a Bi-weekly timesheet and OT is
calculated weekly. How would I tell the spreadsheet was a week is? I
can't change Sheet1, so I am just planning on pointing to it in other
sheets. Thanks in advance.

matiez

Maybe, I am another person working on the same project. I have fixed
half of the problems the other person was having. These are, as I feel,
new problems that have arisen since I looked at it. Help would be much
appreciated.

Brandon

Bob Phillips wrote:
Haven't we been here before on this one?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Start Time End Time Lunch Regular Hours Worked OverTime Total Hours
8:00 AM 4:30 PM 0.5 8 0 8
8:00 AM 5:30 PM 0.5 8 1 9
8:00 AM 5:30 PM 0.5 8 0 8
8:00 AM 3:30 PM 0.5 7 0 7

Reg hours=((C9-B9)*24)+H9-I9-L9 Where L9=OT, H9=Other, and I9=Lunch

OT=IF(M9-80,M9-8,0) where M9=Total Hours Paid. I have modified this
to: IF($M$22-800,(IF(M9-80,M9-8,0)),0) where M22=sum of total hours
paid.

Total hourse paid=((C9-B9)*24)+K9+H9-I9 Where K9=Leave Time (sick,
ect.), H9=Other, and I9=Lunch.

Brandon

Bob Phillips wrote:
Some sample data and existing formulae would help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Here's a complicated problem. I can't change the format of the current
timesheet. The overtime is calculated by taking the total hours worked
and subracting 8. This is fine, but two things break it. 1) When less
than 8 hours are worked to compensate, and 2) when an entire shift is
overtime. These are two really big things that should have been fixed
when the timesheet was first made.

Anyways, I have been working on fixing the problem. So far, I have
created a way of correcting problem #1. I have a running total. If the
total is 0, then OT is displayed, else 0 is displayed. Actually
working overtime breaks this, too, though. Then you end up with both
the shifted OT displayed and the actual OT displayed.

A third problem that arose: It is a Bi-weekly timesheet and OT is
calculated weekly. How would I tell the spreadsheet was a week is? I
can't change Sheet1, so I am just planning on pointing to it in other
sheets. Thanks in advance.

matiez

1.) Whats the difference between 'Regular Hours' and 'Hours Worked' ?

2.) How do I know from looking at this, when a whole shift is overtime ?


wrote in message
oups.com...
Start Time End Time Lunch Regular Hours Worked OverTime Total Hours
8:00 AM 4:30 PM 0.5 8 0 8
8:00 AM 5:30 PM 0.5 8 1 9
8:00 AM 5:30 PM 0.5 8 0 8
8:00 AM 3:30 PM 0.5 7 0 7

Reg hours=((C9-B9)*24)+H9-I9-L9 Where L9=OT, H9=Other, and I9=Lunch

OT=IF(M9-80,M9-8,0) where M9=Total Hours Paid. I have modified this
to: IF($M$22-800,(IF(M9-80,M9-8,0)),0) where M22=sum of total hours
paid.

Total hourse paid=((C9-B9)*24)+K9+H9-I9 Where K9=Leave Time (sick,
ect.), H9=Other, and I9=Lunch.

Brandon

Bob Phillips wrote:
Some sample data and existing formulae would help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Here's a complicated problem. I can't change the format of the current
timesheet. The overtime is calculated by taking the total hours worked
and subracting 8. This is fine, but two things break it. 1) When less
than 8 hours are worked to compensate, and 2) when an entire shift is
overtime. These are two really big things that should have been fixed
when the timesheet was first made.

Anyways, I have been working on fixing the problem. So far, I have
created a way of correcting problem #1. I have a running total. If the
total is 0, then OT is displayed, else 0 is displayed. Actually
working overtime breaks this, too, though. Then you end up with both
the shifted OT displayed and the actual OT displayed.

A third problem that arose: It is a Bi-weekly timesheet and OT is
calculated weekly. How would I tell the spreadsheet was a week is? I
can't change Sheet1, so I am just planning on pointing to it in other
sheets. Thanks in advance.

matiez