Dealing With Tenths Of A Second in Excel

Working with time data, especially with tenths of a second, in Excel is miserable. If you have ever had Excel “help” with its interpretation of it thinks you mean you will know what I mean. Time can literally disappear if you are not careful. In motorsports, whilst possibly more flattering, it is just not useful. This approach shows a method to stop Excel from messing with your data. It is not ideal in many respects but occasionally could be useful for you if you are stuck.

The simple approach will help you keep control, speed your data entry and make sure any calculations are accurate.

Formula To Convert Into Seconds

Use the following to convert your timing data into seconds.

So, for a time of 1 min 23.324 seconds, in Cell A1 input:

1.23.324

Note the full stop (period) after the 1 minute, and not the normal “:“

In another cell then put this formula and press Enter:

=0+TEXT(SUBSTITUTE(A1,".",":",1),"[s].000")

You’ll see 83.324, which is the 1.23.324 time in total seconds.

The benefit is that your time is convert into a proper number, and not “Excel time.”

This means your original number is not changed and you remain in full control.

It might not be ideal as you now have an extra column of data to deal with but if that is ok then it might be worth it to stop Excel messing with your data..

Below, two bonuses in Pro Corner  – a function and a detailed explanation of how this works.

PRO CORNER

1- Think you’ll use this a lot? Try this function.

If you think this is something you’ll use a lot, here is a function you can have to make life easier.

Add the following code to a module in Excel. (Never done that? See this)

Function SPLITTIME(Time As String) As Double
    '(c) Pace Insights 2018
    'www.paceinsights.com
    '=0+TEXT(SUBSTITUTE(A1,".",":",1),"[s].00")
    Dim subTime As String
    With WorksheetFunction
        subTime = .Text(.Substitute(Time, ".", ":", 1), "[s].000")
    End With
    SPLITTIME = subTime
End Function

To use.

In Cell A1 again input:

1.23.324

In another cell, call the function by writing:

"=SPLITTIME(A1)"

This will give you the total time in seconds in that cell.

Same as the formula only more efficient and easier for you.

Again, the original time you entered is not changed.

Again, the time in seconds is a real number, not in “Excel time.”

This function gives you full control and an easier way to input.

Give it a try and let me know how you get on.

2- Detailed Explanation: How does the formula work?

The formula is:

=0+TEXT(SUBSTITUTE(A1,”.”,”:”,1),”[s].000″)

There are three parts to it:

1- SUBSTITUTE

2- TEXT

3- The Plus Zero.

SUBSTITUTE

This function substitutes characters in text.

Our input is:

1.23.324

Excel doesn’t know what to make of that.

It therefore stores it as text.

SUBSTITUTE takes in this text, looks for any “.” and changes them for “:“

The 1 in the formula, says only change the first instance of “.” and ignore any others.

This changes our input to the normal Excel format for time:

[1:23].324

However, as this is held in memory at the moment, it won’t change the number or update the Cell formatting or cause us any grief.

Yay!

TEXT

TEXT is like Custom formatting for cells, or Format in VBA.

The formula formats what SUBSTITUTE has held in memory.

The formatting is specified here:

"[s].000"

This says we want:

The elapsed time in seconds to three decimal places.

The [] specifies the elapsed time part. The “s” the seconds and the three “0” ask for the number of decimal places.

This is still as text.

Quick side: It is crazy to think we’re performing an elapsed time calculation on text.

Maybe that’s just me? Anyhow…

The Magical 0+

Back in the real world, you can’t perform Maths functions on text, only numbers.

That is where the “0+” comes in.

This fools Excel’s cell formatting into changing the text held in memory into a number.

When I first saw this I did think that was pretty clever.

I’m sure you do too now!