The answer will depend largely on where you have this value and how it is
stored.
Excel doesn't store "time"
What it DOES is converts time to a fractional part of a day
(date is stored as the number of days since 1/1/1900)
so, 8:59:44 is converted to 0.374814814814815
and DISPLAYED as
8:59:44 AM
Now, since we know that 60 sec /min * 60 min/hr * 24 hrs/day = 86400 second/day,
then 8:59:44 AM * 86400 = 32384 seconds.
Now, if you're not storing 8:59:44 as time, but as a text string,
then you have to decide what approach you'd like as a solution.
For instance, strictly using Excel functions,
if you put ' 8:59:44 in cell A1, then B1 can be:
=LEFT(A1,FIND(":",A1)-1)*60*60
+MID(A1,FIND(":",A1)+1,2)*60
+RIGHT(A1,2)
Now, if you want to write a VBA function:
Function NumSecs(tVal)
Dim tArray
If (InStr(1, tVal, ":") > 0) Then
tArray = Split(tVal, ":")
NumSecs = Int(tArray(0)) * 3600 + Int(tArray(1)) * 60 + Int(tArray(2))
Else
NumSecs = ""
End If
End Function
then B1 is:
=numsecs(A1)
So... lots of different ways, depending on what you're working with,
what approach you wish to take, and how you want to present the results.
(I put the results in the cell)
Paul
________________________________
From: Venkatesan c <[email protected]>
To: [email protected]
Sent: Wed, March 10, 2010 9:02:37 AM
Subject: $$Excel-Macros$$ How to Convert Time to Sec
8:59:44 into Decemical seconds is there any formula anyone help me
Thanks & Regards
Venkat--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
