Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Function Problem
I keep for myself ice hockey stats, on several teams. I'm have a really hard
time figuring out how to get around the goalie minute numbers. Example say Goalie A played 60:00:00, where the sixty is sixty minutes, and the next number is seconds and then the last is ten of seconds. I have to put this number in my spreadsheet as text becasue Excel will change it everytime I input this as a number. So then I try to convert it to a number so that I can add several numbers together to come up with a total number of minutes played. But everytime that I use the Value forumla it come up with 2.5. Can not for the life of me figure that one out, must just be having a bad day I quess. And just so that you know, the goalie is not always going to have the above number for each game, it could be something quite different every single game. Here is several examples: 51:24:32 or 15:16:29. So any help in pointing where I'm going wrong would be appreciated. Steve Martin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Function Problem
Hi
Are tenths of second really so important. I'm asking because Excel isn't goot at all for such time entries - I myself tried it once (format like "hh:mm:ss.00", I don't remeber exactly anymore), and Excel was able to display properly existing data, but wasn't able to interpret new entries. In form you used in your example, Excel interpreted it as 60 hrs probably - what is 2.5 days (dates and times are in Excel integrated into datetime value, where integer part of it represents days, and fractional part represents times < 24 hours). Simply enter 0:60:0, and all will be OK. Arvi Laanemets "caldog" wrote in message ... I keep for myself ice hockey stats, on several teams. I'm have a really hard time figuring out how to get around the goalie minute numbers. Example say Goalie A played 60:00:00, where the sixty is sixty minutes, and the next number is seconds and then the last is ten of seconds. I have to put this number in my spreadsheet as text becasue Excel will change it everytime I input this as a number. So then I try to convert it to a number so that I can add several numbers together to come up with a total number of minutes played. But everytime that I use the Value forumla it come up with 2.5. Can not for the life of me figure that one out, must just be having a bad day I quess. And just so that you know, the goalie is not always going to have the above number for each game, it could be something quite different every single game. Here is several examples: 51:24:32 or 15:16:29. So any help in pointing where I'm going wrong would be appreciated. Steve Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Function Problem
Thanks I will try it that way.
The way you explained it makes more sense to me now. Steve "Arvi Laanemets" wrote: Hi Are tenths of second really so important. I'm asking because Excel isn't goot at all for such time entries - I myself tried it once (format like "hh:mm:ss.00", I don't remeber exactly anymore), and Excel was able to display properly existing data, but wasn't able to interpret new entries. In form you used in your example, Excel interpreted it as 60 hrs probably - what is 2.5 days (dates and times are in Excel integrated into datetime value, where integer part of it represents days, and fractional part represents times < 24 hours). Simply enter 0:60:0, and all will be OK. Arvi Laanemets "caldog" wrote in message ... I keep for myself ice hockey stats, on several teams. I'm have a really hard time figuring out how to get around the goalie minute numbers. Example say Goalie A played 60:00:00, where the sixty is sixty minutes, and the next number is seconds and then the last is ten of seconds. I have to put this number in my spreadsheet as text becasue Excel will change it everytime I input this as a number. So then I try to convert it to a number so that I can add several numbers together to come up with a total number of minutes played. But everytime that I use the Value forumla it come up with 2.5. Can not for the life of me figure that one out, must just be having a bad day I quess. And just so that you know, the goalie is not always going to have the above number for each game, it could be something quite different every single game. Here is several examples: 51:24:32 or 15:16:29. So any help in pointing where I'm going wrong would be appreciated. Steve Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Function Problem
Your problem is that when you enter 60:00:00, Excel thinks you are entering
hh:mm:ss. 60 hours is 2.5 days which is why you see the resulting number 2.5. The correct way to enter minutes and seconds with decimals is mm:ss.d (you can enter as many decimal places as you like). In your case, you would enter: 60:00.0 Custom format your cell as [mm]:ss.0 to force Excel to display more than 60 minutes. -- Regards, Fred "caldog" wrote in message ... I keep for myself ice hockey stats, on several teams. I'm have a really hard time figuring out how to get around the goalie minute numbers. Example say Goalie A played 60:00:00, where the sixty is sixty minutes, and the next number is seconds and then the last is ten of seconds. I have to put this number in my spreadsheet as text becasue Excel will change it everytime I input this as a number. So then I try to convert it to a number so that I can add several numbers together to come up with a total number of minutes played. But everytime that I use the Value forumla it come up with 2.5. Can not for the life of me figure that one out, must just be having a bad day I quess. And just so that you know, the goalie is not always going to have the above number for each game, it could be something quite different every single game. Here is several examples: 51:24:32 or 15:16:29. So any help in pointing where I'm going wrong would be appreciated. Steve Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mid Function Problem | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
time problem or excel mistake | Excel Worksheet Functions | |||
Time Sheet Function | Excel Worksheet Functions | |||
Date & Time | New Users to Excel |