Time Conversion/Fractions of a Second

[im2fst4u]

Hi guys and gals I got a possible tough question for ya.
I'm doing calculations with lap time and race cars and need the fractions of a second. I got my time to work out with a m.ss.000 format. (need the fractions of a second)
But I'm doing calculations in formulas that require ^2 and things like that, so I need a number, not time. (i've tried, I get dumb numbers like 0:00.002)

I found this formula online to convert time into seconds:
=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1) This works splendidly for what I need but I want the fraction of a second included in the number as this only does a conversion like this:

Ex. 1:20.500 (one minute, twenty and a half seconds)
converts to 81.000(seconds) Now I have a number to use in formulas but with a rounded value.

I need this:
Ex. 1:20.500
80.500 (seconds)

Anyone got a solution?

 

[DILIPandey]

Hi im2fst4u,

If you just need the display in that way, use below formula:-
=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)-1&"."&RIGHT(TEXT(A1,"mm:ss.000"),3)


Regards,
DILIPandey

 

[jsotola]

if you want to work with fractions of a second, then you will have to do all calculations in seconds

do not use any of the time formulas

after you complete the calculations, convert to a string that looks like a time value eg. "23:45.546"

 

[FormR]

I need this:
Ex. 1:20.500
80.500 (seconds)

Anyone got a solution?

You simply multiply by hours in a day (24) * minutes in an hour (60) * seconds in an hour (60) = 86400

Note: You may need to format the cell containing the formula as number.

For example:

Excel Workbook
	A	B
1	Time	Decimal
2	01:20.5	80.500
Sheet1

 

[im2fst4u]

Hi im2fst4u,

If you just need the display in that way, use below formula:-
=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)-1&"."&RIGHT(TEXT(A1,"mm:ss.000"),3)


Regards,
DILIPandey

So I tried this out and it works to give the fractions of a second but gives an inaccurate number
ie. Time:1:20.225, The conversion is:79.225. Shouldn't it be 80.225?

 

[im2fst4u]

You simply multiply by hours in a day (24) * minutes in an hour (60) * seconds in an hour (60) = 86400

Note: You may need to format the cell containing the formula as number.

For example:
Sheet1

	A	B
Time	Decimal

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:73px;"><col style="width:78px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]01:20.5[/TD]
[TD="bgcolor: #ffff00, align: right"]80.500[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =A2*86400 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hey, I tried this and it works great! Plus it's simple and I understand it.

Thanks for the quick replies guys, really helpful.

 

[FormR]

Hey, I tried this and it works great! Plus it's simple and I understand it.

Thanks for the quick replies guys, really helpful.

Glad it works for you, thanks for letting us know.