time as mm:ss to total seconds

Try this

=A1*1440

Row\Col	A	B
1	1:15	75
2	2:05	125

Originally Posted by syncguy

Data (time)
Cell A2= 1:15
Cell A3= 2:05

Desired result ( total seconds)
Cell B2= 75
Cell B3= 125

First, if you literally enter 1:15, Excel will interpret that as 1 hr 15 min, regardless of how you format the cell. Enter either 0:1:15 or 1:15.0, and format as Custom [m]:ss.

Then the formula in B2 can be =A2*86400 or =ROUND(A2*86400,0). Use the latter if you want integer seconds with no fractional part.

Even if the time is entered accurate to the second, like 0:1:15, A2*86400 might have (infinitesimally small) fractional seconds due to the way that binary arithmetic works.

If your data comes from another source, and it must be in the form 1:15 to represent 1 min 15 sec, the formula in B2 can =A2*1440 or =ROUND(A2*1440,0).

Technically, we are actually converting hr:min to minutes. But the computer does not really know that. "Units" of data is a human interpretation.

Originally Posted by syncguy

....having a hard time formatting cells to just show total seconds rather than minutes:seconds format...

To show total seconds you can simply custom format the cell as [s]

.....but as joeu2004 says, you need to make sure you input correctly in the first place, so input as 0:01:15 and format the cell as [s] and you'll see 75

Understand that time is controlled or broken down by a 24 hour period which is why you end up in decimal format. If you are starting with a whole number (13) and you want the hour 13 to display you would simply divide by 24 to get 1:00 PM or 13:00. Now if you are going in the inverse you will multiply the decimal (Which is formatted to appear as a time value format HH:MM or H:MM:SS for your viewing pleasure)

Once you wrap your head around that - then just remember the following list. 24 for hours, that time 60 for Minutes (or just 1440 = 24*60) and then minutes times 60 for seconds (or just 86400 = 24*60*60)

Multiply by = (Simplified)
Hours 24= 24
Minutes (24*60)= 1440
Seconds (24*60*60)= 86400

So...
Time * Conversion = Result
0:01:15 * 24 = 0.020833333 Hours
0:01:15 * 1440 = 1.25 Minutes
0:01:15 * 86400 = 75 Seconds