Time Function Problem

[caldog]

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

 

[Arvi Laanemets]

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]

Thanks I will try it that way.

The way you explained it makes more sense to me now.

Steve

 

[Fred Smith]

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.