formula to convert minutes and seconds into just seconds

chriscim

New Member
Joined
Apr 22, 2002
Messages
30
at work i have to input time for the average
lenght of the call and then convert into seconds.
is there a formula to do so
like a1 is 3:15
in b1 i want a formula to convert the 3:15 into just seconds which would be 195

thanks
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
If cell A1 contains a time value formatted as m:ss then enter the following formula in cell B1...

=A1*86400

If cell A1 contains "3:15" use...

=TIMEVALUE("0:"&A1)*86400
This message was edited by Mark W. on 2002-04-23 11:03
 
Upvote 0
On 2002-04-23 11:11, chriscim wrote:
also do u know what the formula is to convert back into minutes and seconds

thanks
chris

=TEXT(B1/86400,"m:ss")

where B1 contains the seconds as an integer.
 
Upvote 0
On 2002-04-23 11:08, chriscim wrote:
thanks
just what i was looking for
how did u know that formula

chris

Excel stores time values as a fractional part of a day... there are 86,400 seconds in a day. Excel time values are entered as hours:minutes or hours:minutes:seconds so...

=TIMEVALUE("0:"&A1)*86400

...first converts your text string to a time value and then converts that fraction of a day into seconds.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top