Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / August 2006

Tip: Looking for answers? Try searching our database.

How to convert date/time to seconds

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
christopher.marin@gmail.com - 19 Aug 2006 00:23 GMT
I would appreciate any help from an Excel guru out there that can help
me automatically convert a cell with a day:hours:minutes:seconds format
to seconds.  So for example, 0:00:04:44 should be converted to 284.

Any ideas?  Thanks in advance.
Trevor Shuttleworth - 19 Aug 2006 00:46 GMT
You don't actually express time as days, hours, minutes and seconds the way
you have shown it.  It is only hours, minutes and second

Assuming the time is in cell A1 *and* has zero days which can be ignored:

=RIGHT(A1,LEN(A1)-FIND(":",A1))*24*60*60    format as General

If you do have data in the format shown with days as the first element, you
could use:

=LEFT(A1,FIND(":",A1)-1)*24*60*60+RIGHT(A1,LEN(A1)-FIND(":",A1))*24*60*60

or

=(LEFT(A1,FIND(":",A1)-1)+RIGHT(A1,LEN(A1)-FIND(":",A1)))*24*60*60

Regards

Trevor

>I would appreciate any help from an Excel guru out there that can help
> me automatically convert a cell with a day:hours:minutes:seconds format
> to seconds.  So for example, 0:00:04:44 should be converted to 284.
>
> Any ideas?  Thanks in advance.
Pete_UK - 19 Aug 2006 01:23 GMT
Times are stored internally by Excel as fractions of a 24-hour day, so
that 12:00:00 would be stored as 0.5 and 18:00:00 as 0.75.
Consequently, to convert a time to seconds, you must multiply by the
number of seconds in a day - it is easier to remember this as *24*60*60
rather than the actual number (86400).

Hope this helps.

Pete

> I would appreciate any help from an Excel guru out there that can help
> me automatically convert a cell with a day:hours:minutes:seconds format
> to seconds.  So for example, 0:00:04:44 should be converted to 284.
>
> Any ideas?  Thanks in advance.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.