Converting MS to Seconds

问题:

I found this formula to use to turn MS into Seconds but it was written for Excel 2002 and i am using 2010.

=CONCATENATE(TEXT(INT(B1/1000)/86400,"hh:mm:ss"),".",B1-(INT(B1/1000)*1000))

Here are a few examples of what I am trying to take from MS to Seconds

25188
59125
64092
19115
26416

I will be honest i am not very good with Excel formulas so any help would be great. I have taken a screen shot of the error that i am getting with the above formula.

回答1:

It works fine in Excel 2010. You just can't put this formula in cell B1.

If you put a number in B1 and this formula in any other cell, it will work.

You're getting a circular reference warning because the formula refers to cell B1, which is the cell the formula is in.

回答2:

The code you're using isn't actually correct. For instance, your example of 64092 returns 00:01:04.92, when it should return 00:01:04.092.

This is because you're not padding your milliseconds with zeros, like so:

=CONCATENATE(TEXT(INT(B1/1000)/86400,"hh:mm:ss"),".",TEXT(B1-(INT(B1/1000)*1000),"000"))

But you're complicating things.

As @barry-houdini points out, it would me much simpler to use:

=TEXT(B1/86400000,"hh:mm:ss.000")

if you need a text cell for some reason, or just

=B1/86400000

to get the time in days, setting a custom format for the cell to "hh:mm:ss.000".