Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
Hi Jason
Try =SUM(B1:B5) Format the cell containing the formula, FormatCellsNumberCustom [hh]:mm -- Regards Roger Govier "Jason" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
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. "Roger Govier" wrote: Hi Jason Try =SUM(B1:B5) Format the cell containing the formula, FormatCellsNumberCustom [hh]:mm -- Regards Roger Govier "Jason" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
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". "Jason" wrote in message ... 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. "Roger Govier" wrote: Hi Jason Try =SUM(B1:B5) Format the cell containing the formula, FormatCellsNumberCustom [hh]:mm -- Regards Roger Govier "Jason" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
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? "Stephen" wrote: 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". "Jason" wrote in message ... 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. "Roger Govier" wrote: Hi Jason Try =SUM(B1:B5) Format the cell containing the formula, FormatCellsNumberCustom [hh]:mm -- Regards Roger Govier "Jason" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
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.0064583 3333333333}which is an array of the decimal equivalents of the three times. "Jason" wrote in message ... 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? "Stephen" wrote: 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". "Jason" wrote in message ... 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. "Roger Govier" wrote: Hi Jason Try =SUM(B1:B5) Format the cell containing the formula, FormatCellsNumberCustom [hh]:mm -- Regards Roger Govier "Jason" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
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 "Jason" wrote in message ... 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? "Stephen" wrote: 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". "Jason" wrote in message ... 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. "Roger Govier" wrote: Hi Jason Try =SUM(B1:B5) Format the cell containing the formula, FormatCellsNumberCustom [hh]:mm -- Regards Roger Govier "Jason" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Entering Time & Then Adding It
Yes!! That worked, I wasn't formatting the sum cell as [h]:mm:ss
Thanks so much. "Stephen" wrote: 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.0064583 3333333333}which is an array of the decimal equivalents of the three times. "Jason" wrote in message ... 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? "Stephen" wrote: 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". "Jason" wrote in message ... 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. "Roger Govier" wrote: Hi Jason Try =SUM(B1:B5) Format the cell containing the formula, FormatCellsNumberCustom [hh]:mm -- Regards Roger Govier "Jason" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering time | Excel Discussion (Misc queries) | |||
Adding small units of time and entering them quickly | Excel Discussion (Misc queries) | |||
Code for entering time - trying again | Excel Discussion (Misc queries) | |||
Entering and adding time | New Users to Excel | |||
Entering and Adding Time | Excel Worksheet Functions |