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.
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:
Note the full stop (period) after the 1 minute, and not the normal “:”
In another cell then put this formula in and press Enter:
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
For our time of 1 min 23.324 seconds, in Cell A1 again input:
In another cell, call the function by writing:
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.