+ Reply to Thread
Results 1 to 2 of 2

Calculating Idle Time Dependent on Workload

  1. #1
    Registered User
    Join Date
    Manchester, England
    MS-Off Ver

    Calculating Idle Time Dependent on Workload

    Hi guys,

    I'm back again

    So I am working on some data for a support centre, and one of the metrics they measure on is time spent doing naff all... :D

    I have gotten myself confused with the maths for one particular part...

    The maximum workload an employee can have at any one time is 2 tasks. For example;

    Employee starts work at 9am, and finishes at 9:30am (oh the dream)

    In that time the worker completes 2 tasks which both take him the full 30 minutes. His workload would look like he's done 30 minutes of work, when his concurrent rate of work would be 2, because he has completed 1 hours worth of work in 30 minutes. His idle time would be 0, as he was working at full capacity for the duration of his shift.

    The issue then comes if the same worker completes 4 tasks within the 30 minutes. The first two are started at 9am, task 1 takes 10 minutes and task 2 takes 15 minutes, but he doesn't start tasks 3 & 4 until 9:15. Leaving an idle time of 5 minutes (time not working at full capacity). If task 3 takes 10 minutes and task 4 takes 15 minutes, then again there is an additional 5 minutes not working at full capacity from 9:25-9:30, so the total idle time for the day would be 10 minutes. If possible, I would like to be able to calculate this idle time by each individual row, and ideally through power query as a custom column. However if that is not possible, formulas will be just fine

    How can I complete the final column using formulae, and is there any additional data I would require in order to acheive this? I've highlighted the column I'm stuck on

    If there are additional pieces of information I need based on the figures on the workbook, I would need to calculate them manually, and preferably through power query as all of this data is already run through there before it spits it out in a useable format (any help on creating these additional columns through PQ would be greatly appreciated!)
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    MS-Off Ver
    MS 365

    Re: Calculating Idle Time Dependant on Workload

    Hey, let me try to help.

    I'll need to understand a little better what you want to see on the "green" column. Try to just write what you want to see on each row.

    Just an insight that may help. If we turn time in work units, you have 1800 seconds, you said that you can work in tasks simultaneous, this in 3600 work units.
    Can you just subtract work seconds from the work units, and then turn the remaining work units in time?

    If not, try to explain why, so we can help in a better way.

+ 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. [SOLVED] Allocate workload / Rotate Start Point / Equal workload
    By spyac in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-06-2018, 10:59 AM
  2. Excel Idle Time Close
    By MarkSSDM in forum Excel General
    Replies: 3
    Last Post: 04-14-2015, 11:23 AM
  3. Help with Calculating/Graphing Avg Server Process Cycle Time - Excluding Idle Periods
    By kr4z33ch3m15t_78 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2013, 05:55 AM
  4. [SOLVED] Calculate accumulated idle time
    By handerson0603 in forum Excel General
    Replies: 6
    Last Post: 03-28-2012, 04:14 PM
  5. Idle time
    By frequent391 in forum Excel General
    Replies: 1
    Last Post: 07-24-2011, 03:57 PM
  6. Replies: 2
    Last Post: 07-14-2010, 02:14 PM
  7. Get the idle time of an excel application
    By jayender.vs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2007, 02:52 PM


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