How to track timings in MS Excel?

Use a Custom format of ‘hh:mm:ss.000’

Yah, that allows the number to present correctly in the cell. But, when the cell is selected, the formula field still shows “1:10:25 AM”.

I’ll play around with it a bit and see if it’s sufficient.

Thanks!

Yeah, that’s wierd isn’t it. I was figuring what was displayed was what mattered. But that does seem to be an Excel ‘feature’.

One other thing that might help, though I’m not sure how: Excel tracks time as a fractional part of a day. So you can compute hours, minutes and seconds by multiplying the value by 24, taking the remainder and multiplying by 60, taking the remainder of that an multiplying by 60. This takes more than one cell to do (unless you get pretty convoluted), but it might be useful.

Weirdness abounds.

So, here’s something I’m trying now.

I put 0:39:30.00—39 minutes, 30 seconds—in a cell, call it A1, and use the custom format. Looks good.

Then, to get a decimal value I can use, I attempt to divide that value by 60, putting the result in A2. That is, A2 = A1/60. I get 0:00:39.500 in A2 then.

It’s a little right—39.5 does correspond to 39:30—but the “decimal place” is off, appearing to show as 39.5 seconds. (Note that it uses the custom format, too. Trying a standard numeric format like 1234.10 just yields 0.00 in the cell).

Okay, well, I’m wanting the fraction of the hour, so I go ahead and divide by 60 again: A3 = A2/60. That result shows as 0:00:00.658. Way off now.

—

It’s getting confusing at this point. Somehow my division is causing me to lose decimal placement. This kind of thing is common to do, so I’m hoping there’s a somewhat obvious/known solution to this problem.

Huh… I’ll keep playing with it. I get the “right” answer for A2 if I do this:

A2 = A1/60*60*60*24 = 39.5 (using standard number format)

…which is kinda what you said in your quip above, except I don’t do any modulos. :-/