[Solved] Formulas : serial time conversion

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I am seeking a formula that convert serial time in minutes/seconds/10th seconds to a decimal. The source format is mm:ss:0. The minutes, seconds and 10ths are delimited with colons and the minutes may also include commas - 11,345:18:00 . I need this number converted to decimal - 11345.30. Thanks!

  • I'm assuming the value referencing the time that you want to convert is a string (text). With that in mind,


    =VALUE(LEFT(A1,LEN(A1)-6))+VALUE(MID(A1,LEN(A1)-4,2))/60+VALUE(RIGHT(A1,2))/6000


    EDIT: Welcome to the board.


    Regards,

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • Barrie,


    Are you sure it's /6000, and not /3600?


    How about modifying your formula to


    =VALUE(LEFT(A1,LEN(A1)-6))+VALUE(RIGHT(A1,5))*.4



    Rich

    If I've been helpful, let me know. If I haven't, let me know that too. 

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!