|
time clock formulas
I have created a formula for calculating total hours / day
the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Try formatting the sum as;
[h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
I'm not sure what you mean, sorry...
"Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
As they are just decimal numbers now, they should simply add up. What is the
problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Maybe Andrew meant something like this in C2 so the values accumulate
=(B2-A2)*24+C1 and copy down -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Bob Phillips" wrote in message ... As they are just decimal numbers now, they should simply add up. What is the problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
I need each row to have its own total, and then have a total at the bottem of
each column. "Peo Sjoblom" wrote: Maybe Andrew meant something like this in C2 so the values accumulate =(B2-A2)*24+C1 and copy down -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Bob Phillips" wrote in message ... As they are just decimal numbers now, they should simply add up. What is the problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Just use =SUM(C1:C?)
where C? is the last cell with data that you want to include -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I need each row to have its own total, and then have a total at the bottem of each column. "Peo Sjoblom" wrote: Maybe Andrew meant something like this in C2 so the values accumulate =(B2-A2)*24+C1 and copy down -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Bob Phillips" wrote in message ... As they are just decimal numbers now, they should simply add up. What is the problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
I know that, but t does not work
a b c 1 7:30 17:30 10.00 2 7:30 17:30 10.00 3 7:30 10:30 3.00 4 7:15 17:30 10.25 5 7:15 17:30 10.25 #VALUE! Formula for c3 is =(b1-a1)*24 hope ths helps "Peo Sjoblom" wrote: Just use =SUM(C1:C?) where C? is the last cell with data that you want to include -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I need each row to have its own total, and then have a total at the bottem of each column. "Peo Sjoblom" wrote: Maybe Andrew meant something like this in C2 so the values accumulate =(B2-A2)*24+C1 and copy down -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Bob Phillips" wrote in message ... As they are just decimal numbers now, they should simply add up. What is the problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Are you saying that if you use
=SUM(C1:C5) you'll get a value error? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I know that, but t does not work a b c 1 7:30 17:30 10.00 2 7:30 17:30 10.00 3 7:30 10:30 3.00 4 7:15 17:30 10.25 5 7:15 17:30 10.25 #VALUE! Formula for c3 is =(b1-a1)*24 hope ths helps "Peo Sjoblom" wrote: Just use =SUM(C1:C?) where C? is the last cell with data that you want to include -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I need each row to have its own total, and then have a total at the bottem of each column. "Peo Sjoblom" wrote: Maybe Andrew meant something like this in C2 so the values accumulate =(B2-A2)*24+C1 and copy down -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Bob Phillips" wrote in message ... As they are just decimal numbers now, they should simply add up. What is the problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
it is easy Andrew.pse find below copy of excel sheet example;copy it directly
into a sheet. start time ending time working time hours minutes 12:23 14:42 2,00 19 2:19 14:41 14:42 0,00 1 0:01 12:12 14:42 2,00 30 2:30 11:23 14:42 3,00 19 3:19 totals: 7,00 69,00 8 uur en 9 minuten solution a) 8:09 solution b) 8:09 solution c) formula's were written in dutch as you can see below (relevant cellcontents preceeded by an apostrophy) 12:23:00 14:42:00 =UUR(B14-A14) =MINUUT(B14-A14) =TIJD(UUR(B14-A14);MINUUT(B14-A14);SECONDE(I14-J14)) 14:41:00 14:42:00 =UUR(B15-A15) =MINUUT(B15-A15) =TIJD(UUR(B15-A15);MINUUT(B15-A15);SECONDE(I15-J15)) 12:12 14:42 2,00 30 2:30 11:23 14:42 3,00 19 3:19 totaal: =SOM(D14:D17) =SOM(E14:E17) =TEKST.SAMENVOEGEN((D19+INTEGER(E19/60))&" uur en ";REST(E19;60)&" minuten") =TIJD(D19;E19;F19) =SOM(G14:G17) Believe required English formula version as follows: hour minute concatenate integer rest(or remainder??) sum sum time second "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
That's correct
"Peo Sjoblom" wrote: Are you saying that if you use =SUM(C1:C5) you'll get a value error? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I know that, but t does not work a b c 1 7:30 17:30 10.00 2 7:30 17:30 10.00 3 7:30 10:30 3.00 4 7:15 17:30 10.25 5 7:15 17:30 10.25 #VALUE! Formula for c3 is =(b1-a1)*24 hope ths helps "Peo Sjoblom" wrote: Just use =SUM(C1:C?) where C? is the last cell with data that you want to include -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I need each row to have its own total, and then have a total at the bottem of each column. "Peo Sjoblom" wrote: Maybe Andrew meant something like this in C2 so the values accumulate =(B2-A2)*24+C1 and copy down -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Bob Phillips" wrote in message ... As they are just decimal numbers now, they should simply add up. What is the problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
The only way you can get a value error is if you calculate something seen as
text and if C1:C5 are all numbers then you can not get an error, if any value in A1:A5 or B1:B5 are text then you can get a value error in C1:C5 and if you get one there then the sum will return the error as well, what happens if you use =SUMIF(C1:C5,"<#VALUE!") -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... That's correct "Peo Sjoblom" wrote: Are you saying that if you use =SUM(C1:C5) you'll get a value error? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I know that, but t does not work a b c 1 7:30 17:30 10.00 2 7:30 17:30 10.00 3 7:30 10:30 3.00 4 7:15 17:30 10.25 5 7:15 17:30 10.25 #VALUE! Formula for c3 is =(b1-a1)*24 hope ths helps "Peo Sjoblom" wrote: Just use =SUM(C1:C?) where C? is the last cell with data that you want to include -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Andrew" wrote in message ... I need each row to have its own total, and then have a total at the bottem of each column. "Peo Sjoblom" wrote: Maybe Andrew meant something like this in C2 so the values accumulate =(B2-A2)*24+C1 and copy down -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Bob Phillips" wrote in message ... As they are just decimal numbers now, they should simply add up. What is the problem you get, give an example? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andrew" wrote in message ... I'm not sure what you mean, sorry... "Gary''s Student" wrote: Try formatting the sum as; [h]:mm -- Gary's Student "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Assuming that the first seven rows of column A hold the day names, i.e., Mon, Tues, etc. then: - The first seven cells in columns B, C & D should be formatted for 24 hour time (13:30) - The first seven cells in column D would contain the formula: =IF(C1B1,SUM(C1-B1),SUM(C1+24-B1)) "time worked in hrs and mins" - The cells of column E would be formatted as number, say 1 decimal place, and contain the formula: =SUM(HOUR(D1),(MINUTE(D1)/60)) "time worked in hours and tenths - Cell E8 would simply sum E1:E7 for the weekly total in hours and tenths. :cool: -- protonLeah ------------------------------------------------------------------------ protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097 View this thread: http://www.excelforum.com/showthread...hreadid=531198 |
time clock formulas
I've have the same thing. I have built a spreedsheet to kept track of my
hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
=(B1-A1)*24+24*IF(B1A1,0,1)
-- Best regards, --- Yongjun CHEN ================================== - - - - www.XLDataSoft.com - - - - Free Excel/VBA Tool & Training Material ================================== "Ron" wrote in message ... I've have the same thing. I have built a spreedsheet to kept track of my hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Hi Ron
With clock on in A1 and clock off in B1 try =MOD(B1-A1,1) -- Regards Roger Govier "Ron" wrote in message ... I've have the same thing. I have built a spreedsheet to kept track of my hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Roger, I would like to email you my time sheet so you can see if you can
help. I need your email address or a place where I can send this. Ron (IRONMN) -- ironmn "Roger Govier" wrote: Hi Ron With clock on in A1 and clock off in B1 try =MOD(B1-A1,1) -- Regards Roger Govier "Ron" wrote in message ... I've have the same thing. I have built a spreedsheet to kept track of my hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Hi Ron
Send the file to roger dot govier at technology4u dot co dot uk Do the obvious things with dot and at -- Regards Roger Govier "Ron" wrote in message ... Roger, I would like to email you my time sheet so you can see if you can help. I need your email address or a place where I can send this. Ron (IRONMN) -- ironmn "Roger Govier" wrote: Hi Ron With clock on in A1 and clock off in B1 try =MOD(B1-A1,1) -- Regards Roger Govier "Ron" wrote in message ... I've have the same thing. I have built a spreedsheet to kept track of my hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Roger, Hi, i was just checking to see if you got it. Ron
-- ironmn "Roger Govier" wrote: Hi Ron Send the file to roger dot govier at technology4u dot co dot uk Do the obvious things with dot and at -- Regards Roger Govier "Ron" wrote in message ... Roger, I would like to email you my time sheet so you can see if you can help. I need your email address or a place where I can send this. Ron (IRONMN) -- ironmn "Roger Govier" wrote: Hi Ron With clock on in A1 and clock off in B1 try =MOD(B1-A1,1) -- Regards Roger Govier "Ron" wrote in message ... I've have the same thing. I have built a spreedsheet to kept track of my hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Hi Ron
File received and on its way back. Your problem was not in the calculation of time from start to finish. Excel stores times as fractions of a day, so before you multiply the hours worked by the dollar rate, you need to multiply by 24 (hours per day) to convert to decimal hours. In order for the correct result to display, the cell with this calculation needs to be formatted as General or Number (not Time). -- Regards Roger Govier "Ron" wrote in message ... Roger, Hi, i was just checking to see if you got it. Ron -- ironmn "Roger Govier" wrote: Hi Ron Send the file to roger dot govier at technology4u dot co dot uk Do the obvious things with dot and at -- Regards Roger Govier "Ron" wrote in message ... Roger, I would like to email you my time sheet so you can see if you can help. I need your email address or a place where I can send this. Ron (IRONMN) -- ironmn "Roger Govier" wrote: Hi Ron With clock on in A1 and clock off in B1 try =MOD(B1-A1,1) -- Regards Roger Govier "Ron" wrote in message ... I've have the same thing. I have built a spreedsheet to kept track of my hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
time clock formulas
Hi Chen,
Thank you very much! I have been looking for that formula I while ago. I tried and it helped. Thanks! "Daniel CHEN" wrote: =(B1-A1)*24+24*IF(B1A1,0,1) -- Best regards, --- Yongjun CHEN ================================== - - - - www.XLDataSoft.com - - - - Free Excel/VBA Tool & Training Material ================================== "Ron" wrote in message ... I've have the same thing. I have built a spreedsheet to kept track of my hours worked. I go in at 21:45 each nite and get off at 06:00. I can not get the spreedsheet to total the hours. Every outher week I have to work over, I go in at 21:45, but I don't get off until 14:00 the next day, for a total of 16:15. I NEED HELP WITH THIS ONE PLEASE. -- ironmn "Andrew" wrote: I have created a formula for calculating total hours / day the times are entered in military time a1 = 7:30, b1 = 15:30, c1= =(b1-a1)*24, and it works great, however I can not get those numbers in the C colum to add up for a rolling total HELP !!!!! |
| All times are GMT +1. The time now is 09:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com