Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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,

Sama

share|improve this question
1  
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
add comment

migrated from stackoverflow.com Jul 5 '13 at 1:42

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

2 Answers

The formula you need to you is of the form

=(A1/"0:0:1")*100

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
add comment

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

=A1*8640000

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.