Subtracting paid hours from unpaid hours

[Guest]

Hi,

I made a simple timesheet that keeps track of my hours and is set up to go
past the 24 hr mark. What I want to do is subtract my paid hours from my
unpaid hours. So if I have 25:15:00 total hours minus 10:00:00 paid hours it
should come out to 15:15:00 unpaid hours. Total hours is cell G33 with the
formula =SUM(G5:G32), total paid hours is cell G35 with the format of
9/12/2004 10:00:00 AM, and total unpaid hours is cell G37 with the formula
of =G33 - TIME(10,0,0).

What I want is a formula so that all I have to do is change the "Paid hours"
(G35), number and the "Unpaid hours" (G37), will change automatically. See
my timesheet at http://www.misscarlasbbq.com/timesheet.htm Any help would be
greatly appreciated.

Thanks in advance,
Jerry

 

[Charlie O'Neill]

See Chip Pearson's web site, it is an excellent source for information on
Excel
http://www.cpearson.com/excel/overtime.htm

Charlie

 

[Biff]

Hi!

Here's some ideas.

In your HOURS column,column G, I would suppress the
display of the of all the zeros. It will look better! You
can do it by either going to Tools>Options>View and
uncheck Zero Values, or writing your formula in column G
to test for the Time Out cell being blank:

=IF(E1="","",E1-C1)

To get the total paid hours:

What is that "star" symbol?

=SUMIF(J5:J32,"<>",G5:G32)

To get the unpaid hours:

=SUMIF(J5:J32,"="&"",G5:G32)

Total unpaid hours:

=G33-G35

Biff

-----Original Message-----
Hi,

I made a simple timesheet that keeps track of my hours and is set up to go
past the 24 hr mark. What I want to do is subtract my paid hours from my
unpaid hours. So if I have 25:15:00 total hours minus 10:00:00 paid hours it
should come out to 15:15:00 unpaid hours. Total hours is cell G33 with the
formula =SUM(G5:G32), total paid hours is cell G35 with the format of
9/12/2004 10:00:00 AM, and total unpaid hours is cell G37 with the formula
of =G33 - TIME(10,0,0).

What I want is a formula so that all I have to do is change the "Paid hours"
(G35), number and the "Unpaid hours" (G37), will change automatically. See
my timesheet at

http://www.misscarlasbbq.com/timesheet.htm Any help would
be

 

[Biff]

P.S.

Format the cells in column G as [hh]:mm

Biff

 

[Guest]

Hi Biff and Charlie,

Thank you for your prompt reply post. I must admit that I am weak when it
comes to formulas Biff. The star symbol is for my reference only. I copied
and pasted the formulas you gave me. I pasted =SUMIF(J5:J32,"="&"",G5:G32)
into G33 (Total Hours) That value is correct.

Then I pasted =SUMIF(J5:J32,"<>",G5:G32) in cell G35 (Hours Paid), and now
the cell is blank (no value) and I am not sure how column J comes into play
here.

Then I pasted =G33-G35 into cell G37 (Hours Owed), and it gives me the same
value as in G33 (Total Hours) Not sure what is going wrong here.

Biff, if you reply again, can we please speak in terms of Total Hours, Hours
Paid, and Hours Owed so I am sure I am pasting into the correct cells? Also,
there is no format option [hh]:mm There is a [h]:mm:ss but nothing with two
h's in brackets. I am using Excel 2000 if that makes a diff. Keep in mind
that I want to run the hours past 24 so that is why I am using 37:33:55
format.

Is there a way I can post the actual spreadsheet or attach it to an e-mail
for you so you can see the actual columns and rows? You can send me your
email address to (e-mail address removed) if you do not want to post it here and
then I could send you the spreadsheet to open with excel. For now I'll put
the revised sheet at that same web page
http://www.misscarlasbbq.com/timesheet.htm

Thanks you guys and Happy Holidays,
Jerry

 

[Biff]

Hi!

Sent an email.

Biff

-----Original Message-----
Hi Biff and Charlie,

Thank you for your prompt reply post. I must admit that I am weak when it
comes to formulas Biff. The star symbol is for my reference only. I copied
and pasted the formulas you gave me. I pasted =SUMIF (J5:J32,"="&"",G5:G32)
into G33 (Total Hours) That value is correct.

Then I pasted =SUMIF(J5:J32,"<>",G5:G32) in cell G35 (Hours Paid), and now
the cell is blank (no value) and I am not sure how column J comes into play
here.

Then I pasted =G33-G35 into cell G37 (Hours Owed), and it gives me the same
value as in G33 (Total Hours) Not sure what is going wrong here.

Biff, if you reply again, can we please speak in terms of Total Hours, Hours
Paid, and Hours Owed so I am sure I am pasting into the correct cells? Also,
there is no format option [hh]:mm There is a [h]:mm:ss but nothing with two
h's in brackets. I am using Excel 2000 if that makes a diff. Keep in mind
that I want to run the hours past 24 so that is why I am using 37:33:55
format.

Is there a way I can post the actual spreadsheet or attach it to an e-mail
for you so you can see the actual columns and rows? You can send me your
email address to (e-mail address removed) if you do not want to post it here and
then I could send you the spreadsheet to open with excel. For now I'll put
the revised sheet at that same web page
http://www.misscarlasbbq.com/timesheet.htm

Thanks you guys and Happy Holidays,
Jerry

Hi!

Here's some ideas.

In your HOURS column,column G, I would suppress the
display of the of all the zeros. It will look better! You
can do it by either going to Tools>Options>View and
uncheck Zero Values, or writing your formula in column G
to test for the Time Out cell being blank:

=IF(E1="","",E1-C1)

To get the total paid hours:

What is that "star" symbol?

=SUMIF(J5:J32,"<>",G5:G32)

To get the unpaid hours:

=SUMIF(J5:J32,"="&"",G5:G32)

Total unpaid hours:

=G33-G35

Biff
is
cell G33 with the with
the format of change
automatically. See
http://www.misscarlasbbq.com/timesheet.htm Any help would
be

.