Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    break formula into time? (Excel Office 2000)

    Hi,

    I am searching for a way I can calculate time.

    I have a worksheet. attached which calculates how long a pump takes to fill a known volume which is typed in. right now I am using a rough edge method to calculate my time, using if statments. Is there a fuction someone knows of or a way to make a function that can calculate my time with out if statments for hours, mins and seconds?

    if you look at my example I have inputs in green. using the volume I can then calculate a time which is in yellow.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: break formula into time? (Excel Office 2000)

    Excel computes time internally as a fraction of 1 day. Since 1 day = 24*60 = 1440 minutes, you can convert a number of minutes to an Excel time by dividing by 1440, and setting a time format.
    For example, the first time you want to calculate is =D11/D10. If you change this to =D11/D10/1440 and format the result as time, you don't need any additional formulas. Similarly for the other time, you can use =D21/D20/1440 and format as a time.
    Your first example is incorrect, by the way. 2.035002035 minutes (decimal) is not 3:02, of course

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: break formula into time? (Excel Office 2000)

    thanks Hans!

    one last question,

    because the time calculate, a stop watch will record actual time. I want to be able to enter this time in actual as mm:ss and then calculate the variance.
    It seem that it always converts to hh:mm:ss even when I set it up to be custom mm:ss format in the actual time cell D12 and D20. This makes the variance incorrect?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: break formula into time? (Excel Office 2000)

    You *must* enter the time as h:mm or h:mm:ss, regardless of the display format. For example, enter 3 minutes as 0:03 (or even 0:3), and enter 2 minutes 45 seconds as 0:02:45.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: break formula into time? (Excel Office 2000)

    Thats what i thought,

    thank you for you help <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •