+ Reply to Thread
Results 1 to 6 of 6

calculating time durations

Hybrid View

masterintraining calculating time durations 07-17-2020, 01:45 PM
BMV Re: calculating time durations 07-17-2020, 04:15 PM
masterintraining Re: calculating time durations 07-20-2020, 08:33 AM
protonLeah Re: calculating time durations 07-17-2020, 08:17 PM
masterintraining Re: calculating time durations 07-20-2020, 08:50 AM
MrShorty Re: calculating time durations 07-21-2020, 12:14 AM
  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    Madison, Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    8

    calculating time durations

    Running Excel 2010
    Our manufacturing process is start heating metal, stop heating metal, start pouring metal, stop pouring metal. For my purposes "stop heating" and "start pouring" are the same. So I have 3 time values to enter and I want 3 calculated time durations. Length of heating cycle, length of pouring cycle and time between last "stop pouring" to the next "start heating" cycle.
    Raw data is manually entered into one sheet. It's only for me, so style/format doesn't matter. The duration can be displayed as minutes or decimal hours.
    I tried several different ways to format the entered time values but the simple calculations for duration don't work. Right now the time values are formatted as numbers, which works just as well as anything else so far...
    I'll be surprised if I have to apply a custom (if > 60 then -60) type of function but I can't find any canned formulas for getting these durations without the predictable time/number conversion problems. Likewise, I could convert from hours:minutes to hours.hours but do I really have to?
    Thank you for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: calculating time durations

    I'm not sure but
    =REPLACE(RIGHT("0000"&B7,4),3,,":")-REPLACE(RIGHT("0000"&B6,4),3,,":")
    and [m] формат
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-24-2012
    Location
    Madison, Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: calculating time durations

    Thank you BMV! I looked at the example and I will see what I can do to integrate this more fully. Much appreciated.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,960

    Re: calculating time durations

    Entering your times in rows 6-7 without the colon has no advantage and makes all downstream calculations more complicated requiring conversions in the formulas.
    As it is, B17 requires:
    =B$4+(TIME(INT(B7/100),MOD(B7,100),0)-TIME(INT(B6/100),MOD(B6,100),0))
    instead of the simple B7-B6

    You also have a potential problem with the dates, since some poured dates are later than molded dates meaning a cross over midnight which is best handled by adding the date to the time.
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    04-24-2012
    Location
    Madison, Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: calculating time durations

    Hello protonLeah- Thank you for your reply. I use Excel often, but not in a very advanced way, so it's good to get this kind of information you are sending. I like the idea of best practices so I'll add the colon... The only bit I'll say about not using it is that I dislike data entry and always want to reduce keystrokes. 1234 on the number pad is quick. 12shift-colon34 takes longer and I make more mistakes. But if it supports better function later then I'll put them in now.
    I'll work with that formula and your suggestion about date/time. Thanks again-

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,428

    Re: calculating time durations

    I know you've marked this solved, so ignore this if you like. I know a lot of times when I work with times, I have no interest in converting times to Excel's built in system (based on units of days). Many times I will do something like:

    1) Enter times as hh.mm or mm.ss or similar
    2) use the DOLLARDE() function to convert my entry to a decimal hour or decimal minute value =DOLLARDE(hh.mm,60) will convert a hh.mm entry into decimal hours.
    3) Then I can add subtract multiply divide or whatever I need to using those decimal hour values.
    4) If I decide I need to convert anything back to hh.mm, I can use the DOLLARFR() function.

    A lot depends on exactly how you are using these values. In my work, I rarely need them to fit into Excel's calendereing system, so I don't often worry about entering them in that way.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Time Durations
    By ryan.wherry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2018, 01:11 PM
  2. Calculating the average annual cost over 5 years with multiple durations
    By elysiumukexcelforum in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-07-2017, 12:34 PM
  3. Excel 2007 : Adding Time Durations
    By FF-EMT Bourque in forum Excel General
    Replies: 1
    Last Post: 04-13-2012, 01:11 PM
  4. Calculating average durations expressed as hh:mm:ss
    By metalpoker in forum Excel General
    Replies: 7
    Last Post: 09-22-2011, 07:00 AM
  5. Need help calculating breakdown of durations
    By roser in forum Excel General
    Replies: 2
    Last Post: 07-18-2011, 05:10 AM
  6. calculating durations
    By Daniel Black in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2009, 06:01 AM
  7. time durations within time periods (greater than/less than?)
    By rosieb13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-08-2007, 10:38 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1