Dealing with sub-second time in Excel

Time Data in Excel is miserable.

Here is a way to stop Excel messing up your data.

If you’ve ever input timing data in Excel then you’ll appreciate it can be tricky if it has fractions of a second.

You might have even found that when you enter time data, then Excel will remove your fractions of a second, often without you knowing.

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

 

Try this

Use the following approach 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.

Simple but if Excel has ever messed up your timing data, such a relief.

Below, two bonuses in Pro Corner  – a function and an 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!