Converting Excel Time to Elapsed Seconds - Problem with Fractional Seconds

Factual Questions
1

There have been a few questions about converting Excel times in cells (0:00:00.0) in other threads, but I haven’t found the answer to this specific issue.

I have times in a column formatted as above. Each time uses fractional seconds (decimal, one place). If I use the “=SECOND(A2)” function, I get a result of “0.0” even if the actual time shown is 0:00:00.1. If the time in the cell is 0:00:01.0, I get a result of “1.0” back. In other words, the SECOND function doesn’t seem to recognize the fractional seconds, only whole seconds.

I’ve checked that the referenced cells are formatted as time, not text. I’ve checked that the target cells are formatted as numbers with one decimal place. Nothing helps.

My goal is to make “=MINUTE(A2)*60+SECOND(A2)” give me the elapsed time in seconds.

Any suggestions? I’m sure I’m overlooking something very obvious.

2

I’m pretty sure Excel’s time functions only use integer seconds.

3

You could probably code up a function in VBA pretty easily – VBA has access to fractional seconds. I would help, but I’m on a Chromebook right now.

4

Haven’t tried it in Excel, only Google Sheets, but this should give you the fractional second component:
=(TIMEVALUE(A2) * 86400) - FLOOR(TIMEVALUE(A2) * 86400)

Or the number of fractional seconds past each hour:
=(TIMEVALUE(A2) * 86400) - (HOUR(A2) * 3600)

5

The original times are text format. I convert them to time format (which does handle decimal fractions of seconds), but the SECOND(CELL) appears to be integer only.

I’ve wondered if I can add the fractions back somehow by taking the right-most digit. I have literally thousands of entries to convert, so I don’t have the luxury of going through the list manually.

6

I’m away from my laptop, but maybe convert the text to a number using the value() function, then use frac() to get the part after the decimal point. Then add all the parts back together in a cell that has time formatting.

7

OK, it may not be elegant, but I used:

=SECOND(A2)+RIGHT(G2,1)/10

So 00:00:00.1 becomes 0.1 and 00:00:01.1 becomes 1.1 and so forth.

BUUUT…that doesn’t work. SECOND(A2) rounds up to 1 second when the time reaches 00:00:00.6 and makes the value 1.6 seconds.

Drat!

8

One more time!

=MID(G2,3,2)*60+MID(G2,6,2)+RIGHT(G2,1)/10

This seems to do the trick when I convert the times back to text. I can modify it to

=MID(G2,3,2)*60+MID(G2,6,2)+RIGHT(G2,3)/1000

when the time has milliseconds.