Convert time to a usable format in a formula

    Hello all


    Im trying to convert a time formatted as 00:00:00


    Rather than typing in 3 different columns for hrs, min, secs - I would like to be able to enter a time, for example in cell C11 enter 12:25:15 and have this number converted in cell D11 (formatted as number) to a total no of seconds, this being 44715 for the example to the left.


    Currently, I am using =C11*86400 which gives me the correct answer in D11 but the calculation cannot be used in another formula without error.
    I tried converting using n() and Value() but this did not work?


    Any thoughts


    Thank you

    Re: Convert time to a usable format in a formula


    What is the formula you are trying to apply the result to?
    Why can't you use the time in D11 in that formula instead?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Convert time to a usable format in a formula


    HI


    It is a simple division =E12/D11 but there may be more referencing this cell "D11"
    I cant use the time in the cell from D11 as this is not in seconds?
    Unless, there a way to referrence the value in D11 and convert into seconds within the formula so that the answer is in total seconds and this value be recognised as a "number" and used in other calculations.


    Thank you

    Re: Convert time to a usable format in a formula


    unless D11 is 0 or E12 is not numeric, you shouldn't get an error,


    Can you post a small sample workbook showing the issue?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Convert time to a usable format in a formula


    HI


    I made a msitake the format of the cells which have the time entered, also have the date entered.
    They are formatted as dd/mm/yy hh:mm:ss


    I assume this is why calculations dont work?
    However, I do need the date as well as the time to calculate durations that run over 1 day



    Thank you

    Re: Convert time to a usable format in a formula


    Hello,


    With your input cell in C11 ... in order to get your time expressed in seconds,


    you could use following formula :


    Code
    =C11-INT(C11)


    and format the result : Custom [s]


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Convert time to a usable format in a formula


    Hi Carim


    Just to reitterate what I am trying to accomplish...
    I hope this is clear...


    Cell A1: An input cell for a Start date and Time formatted as dd/mm/yy hh:mm:ss
    Cell A2:     A conversion formula for Cell A1 into seconds [ss].
    Cell A3: An input cell for a Stop date and Time     formatted as dd/mm/yy hh:mm:ss
    Cell A4:     A conversion formula for Cell A1 into seconds [ss]. "If the date > A1 as well as times, then the todal days and time duration needs to be calculated"
    Cell A5: A calculation to find the difference in time between A2 & A4 in Seconds formatted as [ss]
    Cell A6: An input cell for Distance in Yards
    Cell A7: A conversion formula into Sixtieths. "Yards*60 for greater accuracy"
    Cell A8: A calculation for Velocity in MPH. =((A7/A5)/1760)*60 (    Velocity = Distance/Time)
    Cell A9:     A calculation for Velocity in YPM "Yards per minute" =(A7/A5)/1760 (Velocity = Distance/Time)


    Using an example of...
    A1: 04/05/17 12:00:00
    A2: = ????? "Formula required here but 43200 is the answer"
    A3:     04/05/17 12:30:00
    A4: = ????? "Formula required here but 45000 is the answer"
    A5: = A2/A4 "1800 is the answer"
    A6: = 1760 "Yards"
    A7: = A6*60 "Yards*60 Greater accuracy"
    A8:     = ((A7/A5)/1760)*60 "2 mph is the answer"
    A9: = (A7/A5)/1760 "58.67 YPM ais the answer"



    Thank you

    Re: Convert time to a usable format in a formula


    Hello again,


    Your explanation is very clear ...:wink:


    But what happens in cell A2 ... if you apply the suggested formula ...and format the result: Format Cells > Number > Custom Type: [s]


    Code
    =A1-INT(A1)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Convert time to a usable format in a formula


    Attached is a sample workbook ... using the data from your example ...


    Hope this will help

    Re: Convert time to a usable format in a formula


    Hi Carim


    Everything was fine up to the point of calculating the YPM and MPH
    Your fromula resolved eh issue.


    I need to get my head around INT and MOD somewhat


    How do I attah sample workbooks? I do not see an option to attach and upload !


    Thank you

    Re: Convert time to a usable format in a formula


    Hi Carim


    Just noticed that the date and time calculations in [s] do not change if the date increases from the start date and time to the stop date and time?


    Any thoughts?


    Thank you

    Re: Convert time to a usable format in a formula


    Hi,


    Glad you could fix your problem ...:wink:


    In order to attach a workbook :


    When you reply ... use the option (bottom right) Go Advanced ... and use the symbol paperclip ... Attachments (to the right of the Smiley..)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Convert time to a usable format in a formula


    Pefect thank you


    Did you see my last post re: date and time calculations not changing?

    Re: Convert time to a usable format in a formula


    Hello again,


    When changing cells A1 and A3 ...all formulas do adjust themselves ...


    So, my assumption is that, you are also facing instances where the Stop Date is higher than the Start Date ...


    To account for these cases, attached is the Version 2 ... for your review ...


    Hope this will help

    Re: Convert time to a usable format in a formula


    Hi Carim


    Sorry, I should have said that A3 is always the same date or greater


    Works great thanks again :cheers:

    Re: Convert time to a usable format in a formula


    Glad you could solve your problem ...


    Thanks for ... your Thanks ..AND for the Beer ...:drunk:


    :thumbcoo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login