Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working with Excel and I need to convert a time unit like this mm:ss.00 (centiseconds) all to centiseconds. There are some formulas to extract Minutes and Seconds from this format but I dont know how can I have all in sentiseconds!

For example, if we have a time unit 02:02.18 it should return us 6218 centiseconds! but how? :( with which formula or macro?!

Thanks in advance,


share|improve this question

migrated from Jul 5 '13 at 1:42

This question came from our site for professional and enthusiast programmers.

Do you know what "multiplication" and "addition" are? – H2CO3 Jul 4 '13 at 23:49
No! only you are the only one who knows what they are! – Sama Ne Jul 5 '13 at 0:42

The formula you need to you is of the form


Explanation: Essentially we are dividing a time by one second, converting it to seconds, and then multiplying it by 100 to get the centiseconds.

Note: Make sure the format of your column is a number if the result doesn't work. A1 is just a placeholder for whatever cell you want to use. Also I believe your conversion is wrong.

If the excel SECOND function returned a floating-point number:

Generally, if you know how to extract minutes and seconds, creating a formula is very straight forward. You know there are 60 seconds in a minute and 100 centiseconds in a second, the total number of centiseconds should be

 =MINUTE(A1)*6000 + SECOND(A1)*100

However the excel SECOND() function only returns integers, and there are no functions that return smaller time steps, so it does not work this way.

share|improve this answer

peanut_butter's answer works for me...but you can also do it this way


format result cell as number or general

That works because excel's default "unit" is the day - you want the number of "centiseconds" and there are 8640000 of those in one day (24*60*60*100)

share|improve this answer

You must log in to answer this question.