I have excel spreadsheet that keeps track of Check In and Check Out Times. Each starting player has a check in time of 8 min (For my basketball starters). If I sub a player in a new player will enter the game with a check in time and the starter will check out with the same time as the new player enters the game. What I’m trying to do, is track the players minutes, but having issues with creating the correct formula to work out the different times.

Players Check In Check Out Check In Check Out Check In Check Out Check In Check Out Check In Check Out Check In Check Out Total Min
1 8.00 1.25 6.75
2 8.00
3 8.00
4 8.00
5 8.00
6 1.75



If you look at player 1 Total time, you will see that this starting player started the game and checked out at 1.25 min, this left my player with a total of 6 min .75 seconds of playing time, however the .75 is not correct and needs formated to determine the correct seconds. I want to calculate the min and seconds and not between dates and time. I need assistance on how to solve this issue and how would I handle all of the check In/Check Out issue?

big0

Does 1,25 mean 1 min and 25 secs or 1 min and 1/4th of a minute ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The 1.25 is the score clock in time, so if you take the start of quarter 1 which is 8.00 min and subtract 1.25 clock time you get an answer of 6.75. The 6. = 6min and .75 is seconds unconverted. Once converted it should read 06:45 (6 min and 45 seconds)

To convert 6.75 to 06:45 use = A1/1440 and format as mm:ss ( A contains the value 6,75)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Pecoflyer, I liked your answer at first but I just reread his question and I think we both misunderstood. Your answer is correct if 1.25 means one and a quarter minutes; the conversion you recommend will produce a result of 1:15. But now that I read more carefully, I think what he's saying is that 1.25 means 1 min 25 sec. If that's so, maybe the solution is simpler:

Big0, who writes the times into this spreadsheet? If it's you, for 1 min 25 sec don't enter "1.25", enter "0:1:25". To Excel that means 0 hr, 1 min, 25 sec. And, as Pecoflyer said, make sure those cells are formatted as "mm:ss", or perhaps as "[m]:ss", so that they'll be displayed properly. Then calculations you do with the time values should work correctly.

That's assuming you want the spreadsheet to work immediately, at courtside. If you're just entering the times at the game and have leisure to work out the totals afterward, then it's probably easier to enter "1.25" at the time and convert them to something you can do proper arithmetic with later. We can tell you how to do that, but I'm guessing you want the results right away, and if so you need to use the colons and the leading 0 so that Excel will know you're talking about minutes and seconds.

There is another possibility: You can have one cell for minutes and the next one for seconds. Using that, you can enter 1<Tab>25, and have the worksheet set up to convert that to 1:25.