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
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" wrote:
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 ToolsOptionsView 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
greatly appreciated.
Thanks in advance,
Jerry
.