I have values such as 2:47 that i need an excel formula to convert it into seconds...2:47=> 167 seconds
However excel keeps recognizing 2:47 as 2:47 AM not as a regular value of minutes to seconds. Help!!!!!!
garbo7441
Favorite Answer
This should also work:
Assume your minutes:seconds value is located in A1
Format A1 as TEXT
Format B1 as NUMBER
In B1 enter the formula: =A1*1440
The conversion result will appear in B1
Note: to convert hours:minutes:seconds into seconds, format the cells the same... A1 as Text, B1 as Number. The formula in B1 would be =A1*86400
?
Minutes To Seconds
IXL@XL
Enter your value required to be converted in cell A1 (formatted as text or enter as such '2:47) In cell B1 enter the following (all one line pls as YA breaks formula)
=VALUE(LEFT(A1,SEARCH(":",A1)
-1)*60+(MID(A1,SEARCH(":",A1)
+1,2)))
Answer comes back in seconds (excuse the pun).
Will convert any number of minutes correctly but not if entered as hours, minutes and seconds. HTH
Anonymous
How do I them change the seconds back into the (2:47) original formula so I can get a total?