Excel cell format question

[Lily the Pink]

I'm doing a number of Time=Distance/Speed calculations, giving a result in seconds. I want to display the result as hh:mm:ss.

In one example, my answer is 187 seconds, which I want to display as 00:03:07, but just changing the cell format to hh:mm:ss comes up with the result 07:40:48. What's going on here, and how can I get my required format (without having to split out the hh, mm and ss and then concatenating them as a string including a pair of : ?

[Meeten-5dulx]

what is the answer to the question when you format as number?
Are you sure the calc is correct?

[Lily the Pink]

The numerical answer of the calculation is 187, which I want to display as 00:03:07. The fact that it is the result of a calculation is, I guess, immaterial.

Ah, but if I just put 187 in a cell and then format it as hh:mm:ss, I have just noticed the formula bar now says "05/07/1900 00:00:00", so it is interpreting it as 187 days since 1/1/1900.

[isaldiri]

Would mm:ss on edit format tab alone work? (Not in front of my pc so can't immediately test this but pretty sure excel can somehow do that conversion easily).

[gazapc]

Does dividing the answer (187) by 86400 (the number of seconds per day) work? Small numbers so rounding might not work so well though.

[CobolMan]

Try dividing your number of seconds by 86400 (no of seconds in a day) then use the hh:mm:ss formatting.

[isaldiri]

CobolMan said:

Try dividing your number of seconds by 86400 (no of seconds in a day) then use the hh:mm:ss formatting.

^ this basically.

[Lily the Pink]

Thanks, that's much better - but with a slight error. 187/87400 formatted as hh:mm:ss gives me 00:03:05, which is 2 seconds adrift, presumably a rounding error. I notice that if I use the function =SECOND(187/87400) the result is 5. Other values give similar errors.

[sociopath]

Lily the Pink said:

Thanks, that's much better - but with a slight error. 187/87400 formatted as hh:mm:ss gives me 00:03:05, which is 2 seconds adrift, presumably a rounding error. I notice that if I use the function =SECOND(187/87400) the result is 5. Other values give similar errors.

86400 not 87400

[Mr Pointy]

Can you post an example of one of the calculations you are perfoming to produce the answer of (say) 187.

[Lily the Pink]

sociopath said:

86400 not 87400

Duh ! Thank-you, that's sorted it.