Entering Time & Then Adding It

[Jason]

I can't seem to figure out how to enter and add time up. I need to enter
time played in a basketball game, then add the totals up at seasons end. For
Example...

Game 1 45:13
Game 2 43:35
Game 3 48:00
Game 4 4:43
Game 5 4:25

Everytime I use mm:ss and cell formula, and turns it into a date, it's
killing me. Please help.

 

[Roger Govier]

Hi Jason

Try
=SUM(B1:B5)
Format the cell containing the formula, Format>Cells>Number>Custom> [hh]:mm

 

[Jason]

That doesn't work, it just converts the input data into a date & time, like
2:44 (2 min, 44 sec) shows as 2:44 am. I can use a general text cell format,
but then I can't do any sum/average.

Hi Jason

Try
=SUM(B1:B5)
Format the cell containing the formula, Format>Cells>Number>Custom> [hh]:mm


--

Regards
Roger Govier

I can't seem to figure out how to enter and add time up. I need to enter
time played in a basketball game, then add the totals up at seasons end.
For
Example...

Game 1 45:13
Game 2 43:35
Game 3 48:00
Game 4 4:43
Game 5 4:25

Everytime I use mm:ss and cell formula, and turns it into a date, it's
killing me. Please help.

 

[Stephen]

All times in Excel are stored as part of a date/time number. The part of the
number before the decimal point is the date (number of days since 31st Dec
1899) and the part after the decimal point is the time (fractions of 24
hours). Formatting (as for any number formatting) does not change the
underlying value, but merely changes the way that value is displayed.

You want to work with minutes and seconds. I think the essential point that
will help you is to realise that you still need to enter the time in hours,
minutes and seconds. For example, enter 45 minutes and 13 seconds as
"0:45:13" (without the quotes). You can stop when you need no more
precision, for example you can enter exactly 45 minutes as "0:45", but you
must have the leading "0:". If you just enter "45:13", Excel will interpret
this as 45 hours and 13 minutes.

Format as you prefer. Let's say you sum three cells containing exactly 45
minutes. Formatting as [h]:mm:ss will display the result as "2:15:00",
whereas [mm]:ss will display it as "135:00".

That doesn't work, it just converts the input data into a date & time,
like
2:44 (2 min, 44 sec) shows as 2:44 am. I can use a general text cell
format,
but then I can't do any sum/average.

Hi Jason

Try
=SUM(B1:B5)
Format the cell containing the formula, Format>Cells>Number>Custom>
[hh]:mm


--

Regards
Roger Govier

I can't seem to figure out how to enter and add time up. I need to
enter
time played in a basketball game, then add the totals up at seasons
end.
For
Example...

Game 1 45:13
Game 2 43:35
Game 3 48:00
Game 4 4:43
Game 5 4:25

Everytime I use mm:ss and cell formula, and turns it into a date, it's
killing me. Please help.

 

[Jason]

Using [h]:mm:ss works great for entering data and it looking right, but doing
a sum then avg doens't work. Currently I have these 3 times listed...
0:02:44
0:09:02
0:09:18

Total should be 21 minutes and 4 seconds, but when I sum =Sum(A1:A3), it
just says 0.0

Any ideas?

All times in Excel are stored as part of a date/time number. The part of the
number before the decimal point is the date (number of days since 31st Dec
1899) and the part after the decimal point is the time (fractions of 24
hours). Formatting (as for any number formatting) does not change the
underlying value, but merely changes the way that value is displayed.

You want to work with minutes and seconds. I think the essential point that
will help you is to realise that you still need to enter the time in hours,
minutes and seconds. For example, enter 45 minutes and 13 seconds as
"0:45:13" (without the quotes). You can stop when you need no more
precision, for example you can enter exactly 45 minutes as "0:45", but you
must have the leading "0:". If you just enter "45:13", Excel will interpret
this as 45 hours and 13 minutes.

Format as you prefer. Let's say you sum three cells containing exactly 45
minutes. Formatting as [h]:mm:ss will display the result as "2:15:00",
whereas [mm]:ss will display it as "135:00".

That doesn't work, it just converts the input data into a date & time,
like
2:44 (2 min, 44 sec) shows as 2:44 am. I can use a general text cell
format,
but then I can't do any sum/average.

Hi Jason

Try
=SUM(B1:B5)
Format the cell containing the formula, Format>Cells>Number>Custom>
[hh]:mm


--

Regards
Roger Govier

I can't seem to figure out how to enter and add time up. I need to
enter
time played in a basketball game, then add the totals up at seasons
end.
For
Example...

Game 1 45:13
Game 2 43:35
Game 3 48:00
Game 4 4:43
Game 5 4:25

Everytime I use mm:ss and cell formula, and turns it into a date, it's
killing me. Please help.

 

[Stephen]

When I try this with your data I get 0:21:04 as expected.
Have you formatted the cell with the =SUM(A1:A3) formula as [h]:mm:ss also?
If you highlight the "A1:A3" part of the formula in the formula bar above
the top of the worksheet and press F9, what do you get? (Press ESC to get
out of this mode.) I get
{0.00189814814814815;0.00627314814814815;0.00645833333333333}which is an
array of the decimal equivalents of the three times.

Using [h]:mm:ss works great for entering data and it looking right, but
doing
a sum then avg doens't work. Currently I have these 3 times listed...
0:02:44
0:09:02
0:09:18

Total should be 21 minutes and 4 seconds, but when I sum =Sum(A1:A3), it
just says 0.0

Any ideas?

All times in Excel are stored as part of a date/time number. The part of
the
number before the decimal point is the date (number of days since 31st
Dec
1899) and the part after the decimal point is the time (fractions of 24
hours). Formatting (as for any number formatting) does not change the
underlying value, but merely changes the way that value is displayed.

You want to work with minutes and seconds. I think the essential point
that
will help you is to realise that you still need to enter the time in
hours,
minutes and seconds. For example, enter 45 minutes and 13 seconds as
"0:45:13" (without the quotes). You can stop when you need no more
precision, for example you can enter exactly 45 minutes as "0:45", but
you
must have the leading "0:". If you just enter "45:13", Excel will
interpret
this as 45 hours and 13 minutes.

Format as you prefer. Let's say you sum three cells containing exactly 45
minutes. Formatting as [h]:mm:ss will display the result as "2:15:00",
whereas [mm]:ss will display it as "135:00".

That doesn't work, it just converts the input data into a date & time,
like
2:44 (2 min, 44 sec) shows as 2:44 am. I can use a general text cell
format,
but then I can't do any sum/average.

:

Hi Jason

Try
=SUM(B1:B5)
Format the cell containing the formula, Format>Cells>Number>Custom>
[hh]:mm


--

Regards
Roger Govier

I can't seem to figure out how to enter and add time up. I need to
enter
time played in a basketball game, then add the totals up at seasons
end.
For
Example...

Game 1 45:13
Game 2 43:35
Game 3 48:00
Game 4 4:43
Game 5 4:25

Everytime I use mm:ss and cell formula, and turns it into a date,
it's
killing me. Please help.

 

[David Biddulph]

And what happens to that result when you format it as [h]:mm:ss too?
Stephen told you how the number is stored, so what number did you expect it
to display if you format 0:21:04 (as a fraction of 24 hours) as a number
with one decimal place?
--
David Biddulph

Using [h]:mm:ss works great for entering data and it looking right, but
doing
a sum then avg doens't work. Currently I have these 3 times listed...
0:02:44
0:09:02
0:09:18

Total should be 21 minutes and 4 seconds, but when I sum =Sum(A1:A3), it
just says 0.0

Any ideas?

All times in Excel are stored as part of a date/time number. The part of
the
number before the decimal point is the date (number of days since 31st
Dec
1899) and the part after the decimal point is the time (fractions of 24
hours). Formatting (as for any number formatting) does not change the
underlying value, but merely changes the way that value is displayed.

You want to work with minutes and seconds. I think the essential point
that
will help you is to realise that you still need to enter the time in
hours,
minutes and seconds. For example, enter 45 minutes and 13 seconds as
"0:45:13" (without the quotes). You can stop when you need no more
precision, for example you can enter exactly 45 minutes as "0:45", but
you
must have the leading "0:". If you just enter "45:13", Excel will
interpret
this as 45 hours and 13 minutes.

Format as you prefer. Let's say you sum three cells containing exactly 45
minutes. Formatting as [h]:mm:ss will display the result as "2:15:00",
whereas [mm]:ss will display it as "135:00".

That doesn't work, it just converts the input data into a date & time,
like
2:44 (2 min, 44 sec) shows as 2:44 am. I can use a general text cell
format,
but then I can't do any sum/average.

:

Hi Jason

Try
=SUM(B1:B5)
Format the cell containing the formula, Format>Cells>Number>Custom>
[hh]:mm


--

Regards
Roger Govier

I can't seem to figure out how to enter and add time up. I need to
enter
time played in a basketball game, then add the totals up at seasons
end.
For
Example...

Game 1 45:13
Game 2 43:35
Game 3 48:00
Game 4 4:43
Game 5 4:25

Everytime I use mm:ss and cell formula, and turns it into a date,
it's
killing me. Please help.

 

[Jason]

Yes!! That worked, I wasn't formatting the sum cell as [h]:mm:ss
Thanks so much.

When I try this with your data I get 0:21:04 as expected.
Have you formatted the cell with the =SUM(A1:A3) formula as [h]:mm:ss also?
If you highlight the "A1:A3" part of the formula in the formula bar above
the top of the worksheet and press F9, what do you get? (Press ESC to get
out of this mode.) I get
{0.00189814814814815;0.00627314814814815;0.00645833333333333}which is an
array of the decimal equivalents of the three times.

Using [h]:mm:ss works great for entering data and it looking right, but
doing
a sum then avg doens't work. Currently I have these 3 times listed...
0:02:44
0:09:02
0:09:18

Total should be 21 minutes and 4 seconds, but when I sum =Sum(A1:A3), it
just says 0.0

Any ideas?

All times in Excel are stored as part of a date/time number. The part of
the
number before the decimal point is the date (number of days since 31st
Dec
1899) and the part after the decimal point is the time (fractions of 24
hours). Formatting (as for any number formatting) does not change the
underlying value, but merely changes the way that value is displayed.

You want to work with minutes and seconds. I think the essential point
that
will help you is to realise that you still need to enter the time in
hours,
minutes and seconds. For example, enter 45 minutes and 13 seconds as
"0:45:13" (without the quotes). You can stop when you need no more
precision, for example you can enter exactly 45 minutes as "0:45", but
you
must have the leading "0:". If you just enter "45:13", Excel will
interpret
this as 45 hours and 13 minutes.

Format as you prefer. Let's say you sum three cells containing exactly 45
minutes. Formatting as [h]:mm:ss will display the result as "2:15:00",
whereas [mm]:ss will display it as "135:00".

That doesn't work, it just converts the input data into a date & time,
like
2:44 (2 min, 44 sec) shows as 2:44 am. I can use a general text cell
format,
but then I can't do any sum/average.

:

Hi Jason

Try
=SUM(B1:B5)
Format the cell containing the formula, Format>Cells>Number>Custom>
[hh]:mm


--

Regards
Roger Govier

I can't seem to figure out how to enter and add time up. I need to
enter
time played in a basketball game, then add the totals up at seasons
end.
For
Example...

Game 1 45:13
Game 2 43:35
Game 3 48:00
Game 4 4:43
Game 5 4:25

Everytime I use mm:ss and cell formula, and turns it into a date,
it's
killing me. Please help.