[Solved] Formulas : serial time conversion

    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!

Register Yourself Login