Timing data in Excel

Timing Data in Excel

An equation and function to help you easily input (and edit) timing data into Excel.

If you’ve ever input timing data in Excel then you’ll appreciate it is not easy.

Especially if it has fractions of a second.

Did you know, that if you enter times the normal way, then Excel will change it and can even remove the fractions of a second without you knowing?

This formula should help with your data entry and any subsequent Maths you need to do i.e. adding up split times etc.

Raw formula

Try using formula to covert your timing data into seconds – as a proper number, not funky “Excel time”.

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 in 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.

1- Your original number in A1 is not changed or truncated at all.

2- The 83.324 is a proper number, and not in Excel time.

Whilst there are two cells needed, the control you get is worth it often.

Or with this User defined 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.

For our time of 1 min 23.324 seconds, 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.

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.

Geek Corner: How 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.

TEXT

TEXT is like Format in VBA.

The formula formats the number held in memory.

The formatting is specified here:

"[s].000"

Specifically this is saying we want:

The elapsed time in seconds to three decimal places.

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

The output is still text, at this point.

A side: Crazy to think we’re performing elapsed time calculations on text.

Maybe that’s just me? Anyhow …

The Magical 0+

You can’t perform Maths functions on text, it needs to be a number.

That is where the “0+” comes in.

This fools Excel’s cell formatting into changing the elapsed time in text is 83.324 to a number.

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

Disclaimer: I actually discovered this formula from the internet looking for something else. Serendipity? Hat tip and link to the Mr Excel guys and thanks.