Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ejerry7
 
Posts: n/a
Default Subtracting paid hours from unpaid hours

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
  #2   Report Post  
Charlie O'Neill
 
Posts: n/a
Default

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

Charlie

"ejerry7" wrote in 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



  #3   Report Post  
Biff
 
Posts: n/a
Default

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
.

  #4   Report Post  
Biff
 
Posts: n/a
Default

P.S.

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

Biff

-----Original Message-----
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
.

.

  #5   Report Post  
ejerry7
 
Posts: n/a
Default

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
.




  #6   Report Post  
Biff
 
Posts: n/a
Default

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 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
.


.

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
Help! I am stuck calculating Days, Hours, Mins please help OB1 Excel Worksheet Functions 3 November 15th 04 06:17 PM


All times are GMT +1. The time now is 08:20 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"