How do I add daily hours and minuets for a weeks pay

[Guest]

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?

 

[Guest]

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

 

[Guest]

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?

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

---

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?

 

[David Biddulph]

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

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?

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

---

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?

 

[Guest]

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

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

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?

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

---
:
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?

 

[Guest]

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?

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

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?

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

---
:
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?

 

[Guest]

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

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

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?

:

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

---
:
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?

 

[David Biddulph]

....

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

 

[Guest]

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?

....

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]

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

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?

....

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

:

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]

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

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?

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

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?

:

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

---
:
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?

 

[Guest]

Thanks again...

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

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?

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

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?

:

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

---
:
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?

 

[Guest]

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

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

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?

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

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?

:

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

---
:
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?