Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min
plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
Enter the times in "h:mm" format,
viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
I used the custom format "h:mm" in the cells where I put the times and when I
type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
It should work, so I guess that you've got at least one of your data values
wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph "JR" wrote in message ... I used the custom format "h:mm" in the cells where I put the times and when I type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
sample 1
typed in time without typing the semi colon 0:00 0:00 0:00 0:00 0:00 0:00 0:00 0:00 Total Sample 1 sample 2 typed in time using the semi colon 2:07 :30 :35 1:10 :10 :59 :15 3:17 Total Sample 2 "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph "JR" wrote in message ... I used the custom format "h:mm" in the cells where I put the times and when I type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
ok...I'm really confused. I'm pretty simple and need real simple instructions.
I open a new excel sheet... then I type in 2.07 (yes 2 point 07) in the first cell (A1) the next cell down (A2) I type in .30 (point 30) the next cell down (A3) I type in .35 (point 35) the next cell down (A4) I type in 1.10 (1 point 10) the next cell down (A5) I type in .10 (point 10) the next cell down (A6) I type in .59 (point 59) the next cell down (A7) I type in .15 (point 15) Now in the next cell down (A8) I use the Sum Icon on the tool bar menu and I get 4.66... But since the above numbers represent hours and minuets turned in daily, I need to convert the 4.66 into 5:46 which is the actual hours worked for that week. In my thinking, if I used counting beads, then for every 60 beads it would become 1 hour, but I would have to count out the beads continuously until the end? that's a lot of beads... Can you help me get it to work in excel? "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph "JR" wrote in message ... I used the custom format "h:mm" in the cells where I put the times and when I type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
"JR" wrote: sample 1 typed in time without typing the semi colon 0:00 0:00 0:00 0:00 0:00 0:00 0:00 0:00 Total Sample 1 sample 2 typed in time using the semi colon 2:07 :30 :35 1:10 :10 :59 :15 3:17 Total Sample 2 "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph "JR" wrote in message ... I used the custom format "h:mm" in the cells where I put the times and when I type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
"JR" wrote in message
... .... sample 2 typed in time using the semi colon 2:07 :30 :35 1:10 :10 :59 :15 3:17 Total Sample 2 If you copy and paste the above list of "times" into Excel you'll see that only the 2:07 and 1:10 are being treated as times, and hence the SUM gives 3:17 as you've seen. The other cells all contain text (in most cases it'll stand out a mile as they tend to be left-justified while numbers are right-justified), and hence these cells are counted as zeroes. If you replace the :30 by 0:30, and onwards for the other times that you've missed, then you'll get the right answer. [If you're not sure whether cells contain text or numbers, you can often tell by temporarily changing the format of the group of cells between Time, Number, General, & Text, and see which cells change & which don't.] David Biddulph "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
thanks...I didn't realize I needed to put a zero before the semi colon to
keep it in the time mode...So yes, by typing a zero, I get the right answer. Thanks again. But now I have another question is there a way I can type in the hours and minuets using a decimal point (being that having to use the semi colon key means lots of extra key strokes) and get an answer in hours and minuets? "David Biddulph" wrote: "JR" wrote in message ... .... sample 2 typed in time using the semi colon 2:07 :30 :35 1:10 :10 :59 :15 3:17 Total Sample 2 If you copy and paste the above list of "times" into Excel you'll see that only the 2:07 and 1:10 are being treated as times, and hence the SUM gives 3:17 as you've seen. The other cells all contain text (in most cases it'll stand out a mile as they tend to be left-justified while numbers are right-justified), and hence these cells are counted as zeroes. If you replace the :30 by 0:30, and onwards for the other times that you've missed, then you'll get the right answer. [If you're not sure whether cells contain text or numbers, you can often tell by temporarily changing the format of the group of cells between Time, Number, General, & Text, and see which cells change & which don't.] David Biddulph "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
If you enter times with a decimal point between hours & minutes, instead of
a colon, you can convert to real times the formula =TIME(INT(A1),100*MOD(A1,1),0) -- David Biddulph "JR" wrote in message ... thanks...I didn't realize I needed to put a zero before the semi colon to keep it in the time mode...So yes, by typing a zero, I get the right answer. Thanks again. But now I have another question is there a way I can type in the hours and minuets using a decimal point (being that having to use the semi colon key means lots of extra key strokes) and get an answer in hours and minuets? "David Biddulph" wrote: "JR" wrote in message ... .... sample 2 typed in time using the semi colon 2:07 :30 :35 1:10 :10 :59 :15 3:17 Total Sample 2 If you copy and paste the above list of "times" into Excel you'll see that only the 2:07 and 1:10 are being treated as times, and hence the SUM gives 3:17 as you've seen. The other cells all contain text (in most cases it'll stand out a mile as they tend to be left-justified while numbers are right-justified), and hence these cells are counted as zeroes. If you replace the :30 by 0:30, and onwards for the other times that you've missed, then you'll get the right answer. [If you're not sure whether cells contain text or numbers, you can often tell by temporarily changing the format of the group of cells between Time, Number, General, & Text, and see which cells change & which don't.] David Biddulph "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
See the answer to your similar question elsewhere in this thread.
You can't convert the 4.66 into 5.46, because in adding your decimals you've turned 100 after the decimal point into 1 before, and (as you realised) you needed to turn 60 after the separator into 1 before. -- David Biddulph "JR" wrote in message ... ok...I'm really confused. I'm pretty simple and need real simple instructions. I open a new excel sheet... then I type in 2.07 (yes 2 point 07) in the first cell (A1) the next cell down (A2) I type in .30 (point 30) the next cell down (A3) I type in .35 (point 35) the next cell down (A4) I type in 1.10 (1 point 10) the next cell down (A5) I type in .10 (point 10) the next cell down (A6) I type in .59 (point 59) the next cell down (A7) I type in .15 (point 15) Now in the next cell down (A8) I use the Sum Icon on the tool bar menu and I get 4.66... But since the above numbers represent hours and minuets turned in daily, I need to convert the 4.66 into 5:46 which is the actual hours worked for that week. In my thinking, if I used counting beads, then for every 60 beads it would become 1 hour, but I would have to count out the beads continuously until the end? that's a lot of beads... Can you help me get it to work in excel? "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph "JR" wrote in message ... I used the custom format "h:mm" in the cells where I put the times and when I type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
Thanks again...
"David Biddulph" wrote: See the answer to your similar question elsewhere in this thread. You can't convert the 4.66 into 5.46, because in adding your decimals you've turned 100 after the decimal point into 1 before, and (as you realised) you needed to turn 60 after the separator into 1 before. -- David Biddulph "JR" wrote in message ... ok...I'm really confused. I'm pretty simple and need real simple instructions. I open a new excel sheet... then I type in 2.07 (yes 2 point 07) in the first cell (A1) the next cell down (A2) I type in .30 (point 30) the next cell down (A3) I type in .35 (point 35) the next cell down (A4) I type in 1.10 (1 point 10) the next cell down (A5) I type in .10 (point 10) the next cell down (A6) I type in .59 (point 59) the next cell down (A7) I type in .15 (point 15) Now in the next cell down (A8) I use the Sum Icon on the tool bar menu and I get 4.66... But since the above numbers represent hours and minuets turned in daily, I need to convert the 4.66 into 5:46 which is the actual hours worked for that week. In my thinking, if I used counting beads, then for every 60 beads it would become 1 hour, but I would have to count out the beads continuously until the end? that's a lot of beads... Can you help me get it to work in excel? "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph "JR" wrote in message ... I used the custom format "h:mm" in the cells where I put the times and when I type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add daily hours and minuets for a weeks pay
This works until the hours reach 12 then who knows what it does? How can I
get the actual total time as it goes past 12? Jeanine "David Biddulph" wrote: See the answer to your similar question elsewhere in this thread. You can't convert the 4.66 into 5.46, because in adding your decimals you've turned 100 after the decimal point into 1 before, and (as you realised) you needed to turn 60 after the separator into 1 before. -- David Biddulph "JR" wrote in message ... ok...I'm really confused. I'm pretty simple and need real simple instructions. I open a new excel sheet... then I type in 2.07 (yes 2 point 07) in the first cell (A1) the next cell down (A2) I type in .30 (point 30) the next cell down (A3) I type in .35 (point 35) the next cell down (A4) I type in 1.10 (1 point 10) the next cell down (A5) I type in .10 (point 10) the next cell down (A6) I type in .59 (point 59) the next cell down (A7) I type in .15 (point 15) Now in the next cell down (A8) I use the Sum Icon on the tool bar menu and I get 4.66... But since the above numbers represent hours and minuets turned in daily, I need to convert the 4.66 into 5:46 which is the actual hours worked for that week. In my thinking, if I used counting beads, then for every 60 beads it would become 1 hour, but I would have to count out the beads continuously until the end? that's a lot of beads... Can you help me get it to work in excel? "David Biddulph" wrote: It should work, so I guess that you've got at least one of your data values wrong. It may be easier for us to help you if you tell us what answer you get. -- David Biddulph "JR" wrote in message ... I used the custom format "h:mm" in the cells where I put the times and when I type in the numbers it turns to 0:00...and then I typed it in as 2:07 etc and formated the cell for where the total is to be to "[h]":mm and it returns a different answer than 5:46??? What am I doing wrong? "Max" wrote: Enter the times in "h:mm" format, viz. input in A1:A7 as: 2:07 0:30 0:35 1:10 0:10 0:59 0:15 Then just sum it up in A8: =SUM(A1:A7) Format A8 as Custom, Type: [h]:mm (with the square brackets around the "h") to enable it to rollover 24 hours, if necess. For the sample data, A8 will return: 5:46 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "JR" wrote: Example: 2 hrs 7 min plus 30 min plus 35 min plus 1 hr 10 min plus 10 min plus 59 min plus 15 min??? I know the answer is 5 hours and 46 min but how can I get excel to give me this answer? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hours to minuets | Excel Discussion (Misc queries) |