convert hh:mm:ss-hh:mm:ss to seconds-seconds
I am using Excel 2010 and I am trying to convert times shown in the format hh:mm:ss - hh:mm:ss to seconds-seconds. Here is an example, I am doing something with movies, but for editing purposes in need it in seconds instead of the hour:minute:seconds format. For example in need to convert from 00:35:43 - 00:37:10 to 2143 - 2230. Here are are few more examples of what I need to convert to seconds format...
1:30:13 - 1:33:07
1:00:09 - 1:02:57
Does anybody have any idea how I can do this?
Last edited by lrc_secretary; 10-11-2012 at 11:34 AM.
Assuming your data is in A2, you can use this formula:
=LEFT(A2,SEARCH(" -",A2)-1)*60*60*24 &" - "&RIGHT(A2,LEN(A2)-SEARCH("- ",A2)-1)*60*60*24
Hope this helps.
Pete
Thank you! that solved it for me.
If some of the times were entered as just mm:ss-mm:ss how can I adjust the formula?
It's a bit messy, but you could do this:
=LEFT(A2,SEARCH(" -",A2)-1)*if(len(LEFT(A2,SEARCH(" -",A2)-1))<=5),1,60)*60*24 &" - "&RIGHT(A2,LEN(A2)-SEARCH("- ",A2)-1)*if(len(RIGHT(A2,LEN(A2)-SEARCH("- ",A2)-1))<=5,1,60)*60*24
Hope this helps.
Pete
Another way:
=TEXT(TIMEVALUE(TRIM(LEFT(A2,8))),"[ss]")&" - "&TEXT(TIMEVALUE(TRIM(RIGHT(A2,8))),"[ss]")
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks