+ Reply to Thread
Results 1 to 8 of 8

Calculate 'ON' time for moving time period.

  1. 01-07-2020, 11:51 AM #1
    Charlie_B
    Charlie_B is offline
    Registered User
    Join Date
    12-30-2019
    Location
    Toronto
    MS-Off Ver
    365
    Posts
    10

    Calculate 'ON' time for moving time period.

    I need to calculate the amount of time a person has been operating a piece of machinery for to the following rule...

    "No person may operate the machine for more than 4 hours of any 6 hour period at any time"

    The time frame is 24 hours and the there are 4 operators.
    The calculation for total operating time per person is simple enough for the first 6 hours but once the time passes 6 hours the target is constantly moving.
    I will have a start time and end time for each persons operating stint, I want to calculate their "ON" time for any previous 6 hour period (i.e. NOW - 6 hours) and I also want to calculate the current operators total on time in the previous 6 hours from any given time (it will always be NOW - 6 hours).

    If someone can help me that would be greatly appreciated!
    Reply With QuoteRegister To Reply
  2. 01-07-2020, 11:54 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,445

    Re: Calculate 'ON' time for moving time period.

    Administrative note

    Welcome to the forum

    missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 01-07-2020, 08:48 PM #3
    Charlie_B
    Charlie_B is offline
    Registered User
    Join Date
    12-30-2019
    Location
    Toronto
    MS-Off Ver
    365
    Posts
    10

    Re: Calculate 'ON' time for moving time period.

    Example sheet attached, button does not work, it is a desired end state for 1 click use.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-08-2020, 10:51 AM #4
    Charlie_B
    Charlie_B is offline
    Registered User
    Join Date
    12-30-2019
    Location
    Toronto
    MS-Off Ver
    365
    Posts
    10

    Re: Calculate 'ON' time for moving time period.

    Attached is a 2nd layout option that may be easier to run the calculations on.
    Any help with this would be great, I'm completely stuck.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-10-2020, 12:41 PM #5
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    Re: Calculate 'ON' time for moving time period.

    Perhaps this will be of some help.
    As for summing the total run time*: =SUMPRODUCT(IFERROR(C4:C13-B4:B13,0))
    As for stint not yet finished: =IF(B6<>"",$O$1,"")
    As for run time last 6 hours: =SUM(D4:D13)
    Note that helper columns are added to each driver to calculate operating time using: =IF(OR(C4="",C4<=$O$2),0,C4-MAX(B4,$O$2))
    Note that the helper columns may be hidden for aesthetic purposes.
    *Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    As to the comment in cell B5: A value and a formula can not exist in the same cell, once the actual start time is typed into the cell it would overwrite the formula. There might be a way to use a formula to tentatively populate cells (which would be overwritten when the actual time was input) providing the drivers took turns in order, however looking at the sample provided that is not always the case.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  6. 01-10-2020, 07:53 PM #6
    Charlie_B
    Charlie_B is offline
    Registered User
    Join Date
    12-30-2019
    Location
    Toronto
    MS-Off Ver
    365
    Posts
    10

    Re: Calculate 'ON' time for moving time period.

    JeteMc,

    Thank you so much, going to have a play with it over the weekend to understand further the formula and play with aesthetic as you suggest.
    Awesome
    Reply With QuoteRegister To Reply
  7. 01-15-2020, 11:00 AM #7
    Charlie_B
    Charlie_B is offline
    Registered User
    Join Date
    12-30-2019
    Location
    Toronto
    MS-Off Ver
    365
    Posts
    10

    Re: Calculate 'ON' time for moving time period.

    Is it possible to lock the time format in a cell?
    Due to the nature of the time period crossing over 00:00:00 the date is necessary, however it doesn't need to be seen.
    When I update a start or finish time the format reverts from h:mm:ss to m/d/yyyy h:mm, can this be stopped?
    Reply With QuoteRegister To Reply
  8. 01-17-2020, 10:29 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    Re: Calculate 'ON' time for moving time period.

    Not a problem that I am familiar with and I can not find anything useful.
    It may be that there is a VBA solution, so you may want to go ahead and mark this thread as 'Solved' using the thread tools menu above your first post, and then open a new thread in the Excel Programming / VBA / Macros forum concerning this issue.
    Let us know if you have any questions.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Categorize time ranges and calculate events within the time period
    By yklanka in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-09-2019, 10:16 AM
  2. [SOLVED] Calculate the portion of a planned number during a period, based on another time period
    By BryGuy81 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2017, 01:29 PM
  3. Calculate Time Period -
    By Matt1998 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-09-2014, 11:11 AM
  4. [SOLVED] How to calculate the average for specific Time period
    By bssol in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-15-2013, 02:21 AM
  5. [SOLVED] Complicated:Calculate time period based on no. of units sold + fluctuations within period
    By omaha.crab in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 03:04 AM
  6. Trying to understand a formula to calculate elapsed time in a specific time period
    By lvirden in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-27-2011, 08:21 AM
  7. [SOLVED] Calculate percentage of time period
    By Ricardo Monteiro :^) in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2006, 10:50 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1