28 Jun Stop Excel messing up your timing data.

Timing Data in Excel is miserable.

Here is a formula equation to help you stop Excel messing up your timing data.

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.

Try this formula

Try using this formula to convert your timing data into seconds – as in into 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 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. Check the formatting!

Whilst there are two cells needed, you’ll appreciate the control you keep (especially over the fractions of a second), plus the many more options this opens up.

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

Below, two bonuses in Pro Corner.

Enjoy.

P.s. Like this article? Then sign up below to be the first to hear more insights:

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!

Disclaimer: I actually discovered this formula from the internet looking for something else. Serendipity? I guess you’ve got to recognise how it can help too. Hat tip and link to the Mr Excel guys and thanks.

Don’t forget. Like this article? Then sign up below to be the first to hear more insights:

Pace Insights Ltd. ©2018 All rights reserved.