Forum Discussion

Sethlemley's avatar
Sethlemley
Copper Contributor
Feb 25, 2023

Time conversion

I'm trying to convert minutes:seconds into minutes

ex. In cell A1 "2:45.72" which is 2 minutes and 45.72 seconds. I've tried the formula =MINUTE(A1)*60+SECOND(A1). But that returns a value of 166 it dropped the decimal it should be 166.72?  I've increased the decimal places in the cell format but it is still rounding. 

Formulas and Functions

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 25, 2023

    Sethlemley 

    Use the formula

    =86400*A1

    and format the cell with the formula as General or as Number with 2 decimal places.

    (Excel stores times as numbers, with 1 day as unit. So to convert time to seconds, multiply with 86400, the number of seconds in 1 day)

    • FikturFox's avatar
      FikturFox
      Brass Contributor
      Feb 25, 2023
      A workaround:
      =MINUTE(A1)*60 + SECOND(A1) + RIGHT(TEXT(A1,"mm:ss.00"),3)*1

Resources