Results 1 to 4 of 4

Thread: time format

  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi
    I dont know how I can take a volume (Liters) and divide that by a rate (Liters / min) and format the answer in mm:ss

    Anyone know what I need to do to the formula to make this work?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Blueray,

    I'm making a couple of assumptions here but if A1 contains liters and B1 contains liters/SECOND then =Time(0,0,A1/B1) will return the right amount of time if you format the cell as MM:SS.

    The only way I could get this to work was to use LITERS/SECOND because the TIME function will automatically convert the seconds to the proper number hours/minutes/seconds, but will not convert fractional minutes to seconds for some reason...go figure.

    When converting the liters/min to liters/sec I would you suggest you use a round function {=Round(liters-min/60,decimal-places)} where you can control the precision you want in decimal places.

    Hope this helps.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You need to Convert the answer to a fraction of a Day in order to use a time format

    So if A2 contains the Volume and B2 contains the Rate then the formula for the time
    to use an mm:ss time format is =((A2/B2)/60)/24

    For example If Vol =1000 L and Rate = 45.6 L/min this give 21.93 as a decimal min
    which is 21:56 converted to a time where 21.93 min = 0.015229 Days
    Andrew

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AKW View Post
    You need to Convert the answer to a fraction of a Day in order to use a time format

    So if A2 contains the Volume and B2 contains the Rate then the formula for the time
    to use an mm:ss time format is =((A2/B2)/60)/24

    For example If Vol =1000 and Rate = 45.6 this give 21.93 as a decimal min
    which is 21:56 converted to a time where 21.93 min = 0.015229 Days
    WORKS GREAT THANKS!

Posting Permissions

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