Forum Discussion
Hour format conversion hhmmss.00
In a lot of files I have hours fomated as hhmmss.000 but when in try to format it the content change.
For example :
Raw data : 132425.44 (13h24m25.44s)
And after formatting with hhmmss.00 the value is 103336.00
How can I do ti format correctly the cells ?
4 Replies
Excel does not recognize 132425 as a time. You can use a formula to convert the number 132425.44 to a valid time. Let's say the value is in A2. Enter the following formula in B2:
=TIME(INT(A2/10000),MOD(INT(A2/100),100),INT(MOD(A2,100)))+MOD(A2,1)/86400
and format B2 as hh:mm:ss.00. You can fill this down if required.
Perhaps INT() is not required
=TIME(A2/10000,MOD(A2/100,100), MOD(A2,100) )+MOD(A2,1)/86400shall return the same
You're correct.
- Yes I have try this solution. It's functionnal but I hoped that a more elegant solution exist.
