Convert Seconds and decimals to time

B

bagoxc

Hello,
I'm exporting some information from a system called CMS. The problem
I'm having is that it exports in seconds and decimals. For instance
3:19 (3 minutes 19 seconds) is exporting as 226.605636597. I'm trying
to convert this back to 3:19. Any suggestions?

Thanks in advance!

Chuck
 
P

Pete

I don't see how 3 minutes 19 seconds relates to 226.6056366597 - surely
it is 199 seconds!

If you want to convert 199 (seconds) to time in Excel, you need to
divide by 60 (minutes) divide by 60 (hours) then divide by 24 (days),
as Excel stores time internally as fractions of a day. So, the formula
would be;

=A1 / 60 / 60 / 24

assuming your "seconds" are in cell A1. You need to format the cell as
time or using a custom format.

Pete
 
P

Peo Sjoblom

Then there must be more to it than meets the eye, surely you can convert
3 minutes and 48 seconds in your head and see that it should be
228 seconds? Your data converted returns 03:47.786 formatted as
mm:ss.000

Maybe you can round to the nearest second?

=ROUND(A1/86400/TIME(,,1),0)*TIME(,,1)

where A1 holds the decimal format

format result as mm:ss or hh:mm:ss
 
Top