# Converting Excel Time to Elapsed Seconds - Problem with Fractional Seconds **URL:** \ **Category:** Factual Questions\ **Created:** [February 20, 2023, 3:28pm UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088 "2023-02-20T15:28:38Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![ZonexandScout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zonexandscout/32/544_2.png) [@ZonexandScout](https://boards.straightdope.com/u/ZonexandScout)\ **Post date:** [February 20, 2023, 3:28pm UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/1 "2023-02-20T15:28:38Z")
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. ---
**Author:** ![Typo\_Knig](https://avatars.discourse-cdn.com/v4/letter/t/cdc98d/32.png) [@Typo\_Knig](https://boards.straightdope.com/u/Typo_Knig)\ **Post date:** [February 20, 2023, 9:32pm UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/2 "2023-02-20T21:32:56Z")
I’m pretty sure Excel’s time functions only use integer seconds. ---
**Author:** ![RitterSport](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rittersport/32/6326_2.png) [@RitterSport](https://boards.straightdope.com/u/RitterSport)\ **Post date:** [February 20, 2023, 9:39pm UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/3 "2023-02-20T21:39:43Z")
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. ---
**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\ **Post date:** [February 21, 2023, 1:53am UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/4 "2023-02-21T01:53:13Z")
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)` ---
**Author:** ![ZonexandScout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zonexandscout/32/544_2.png) [@ZonexandScout](https://boards.straightdope.com/u/ZonexandScout)\ **Post date:** [February 21, 2023, 3:33am UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/5 "2023-02-21T03:33:14Z")
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. ---
**Author:** ![Typo\_Knig](https://avatars.discourse-cdn.com/v4/letter/t/cdc98d/32.png) [@Typo\_Knig](https://boards.straightdope.com/u/Typo_Knig)\ **Post date:** [February 21, 2023, 3:53am UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/6 "2023-02-21T03:53:50Z")
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. ---
**Author:** ![ZonexandScout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zonexandscout/32/544_2.png) [@ZonexandScout](https://boards.straightdope.com/u/ZonexandScout)\ **Post date:** [February 21, 2023, 4:54am UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/7 "2023-02-21T04:54:25Z")
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! ---
**Author:** ![ZonexandScout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zonexandscout/32/544_2.png) [@ZonexandScout](https://boards.straightdope.com/u/ZonexandScout)\ **Post date:** [February 21, 2023, 5:03am UTC](https://boards.straightdope.com/t/converting-excel-time-to-elapsed-seconds-problem-with-fractional-seconds/980088/8 "2023-02-21T05:03:42Z")
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.