Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |