Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
bagoxc
 
Posts: n/a
Default Convert Seconds and decimals to time


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


--
bagoxc
------------------------------------------------------------------------
bagoxc's Profile: http://www.excelforum.com/member.php...o&userid=30084
View this thread: http://www.excelforum.com/showthread...hreadid=497654

  #2   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default Convert Seconds and decimals to time

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
bagoxc
 
Posts: n/a
Default Convert Seconds and decimals to time


try this example

3:48 is exporting as 227.785720825

Thanks

Chuck


--
bagoxc
------------------------------------------------------------------------
bagoxc's Profile: http://www.excelforum.com/member.php...o&userid=30084
View this thread: http://www.excelforum.com/showthread...hreadid=497654

  #5   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Convert Seconds and decimals to time

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


--

Regards,

Peo Sjoblom

"bagoxc" wrote in
message ...

try this example

3:48 is exporting as 227.785720825

Thanks

Chuck


--
bagoxc
------------------------------------------------------------------------
bagoxc's Profile:

http://www.excelforum.com/member.php...o&userid=30084
View this thread: http://www.excelforum.com/showthread...hreadid=497654





  #6   Report Post  
Posted to microsoft.public.excel.misc
bagoxc
 
Posts: n/a
Default Convert Seconds and decimals to time


Thanks Sandy! That gives me what I needed.

Chuck


--
bagoxc
------------------------------------------------------------------------
bagoxc's Profile: http://www.excelforum.com/member.php...o&userid=30084
View this thread: http://www.excelforum.com/showthread...hreadid=497654

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting time Dave New Users to Excel 2 September 16th 05 12:30 AM
How to enter current static time in Excel in 00:00:00.0 format? Wlumkong Excel Worksheet Functions 3 May 12th 05 03:54 PM


All times are GMT +1. The time now is 03:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"