+ Reply to Thread
Results 1 to 2 of 2

Calculating total time for individual with multiple start/end times

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    18

    Calculating total time for individual with multiple start/end times

    Good Morning All,

    I am trying to come up with a formula that calculates total time someone has worked in a day. The scenario is an individual will work at a home and start working with an individual. Their start/end times look like this in a pivot:
    Min Start Max Start Min End Max End
    Location A+Counselor A 8:56 AM 4:01 PM 1:11 PM 7:00 PM
    Location A+Counselor B 12:00 AM 8:00 PM 6:00 AM 11:59 PM
    Location B:Counselor C 7:00 AM 12:00 PM 2:00 PM 4:00 PM
    Location C+Counselor D 8:00 AM 8:00 AM 4:00 PM 4:00 PM

    Some people work split shifts while others work a straight shift. The formula I created was this:

    =IF(OR(B9=C9,E9=D9,D9=C9),E9-B9,IF(D9>C9,((E9-D9)+(C9-B9)),IF(C9>D9,((D9-B9)+E9-C9),"New Formula Needed")))*24 (I use a pivot table to show max min for start and end times)

    This works great except for the individuals that have multiple punches during the same time frame. The one scenario I am having trouble solving for is when someone punches in more than once during their shift displaying. This occurs when a counselor starts a shift working with one person but then adds another person mid shift. An example of this could be:

    Location A+Counselor E Min Start Max Start Min End Max End
    Consumer 1 1:00 PM 1:00 PM 8:30 PM 8:30 PM Total Time: 7.5
    Consumer 2 12:00 PM 12:00 PM 2:35 PM 2:35 Pm Total Time: 2.6

    Pivot says that they worked a total of 10.1 because it is grabbing the max and mins and calculating. The actual total time worked is 8.5 hours in reality.

    The raw data comes in like so: Location Counselor Consumer Start Time End Time
    A A A 1:00 PM 8:30 PM
    A A B 12:00 PM 2:35 PM
    A A C 12:00 PM 5:00 PM

    Is this solvable with a formula or am I completely off my rocker? Thanks!

    Sincerely,

    John

  2. #2
    Registered User
    Join Date
    01-27-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Calculating total time for individual with multiple start/end times

    P.S. To clarify in case someone has a question about it . The reason why the times come in this way is because we pay staff for total hours but bill consumers by contact hours. The system we have grabs the times based on individual instead of total hours worked and our "database" peeps say this is the only way we can get the data...I saw bah humbug to that but that is another story!

+ 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] Creating a graph with start times and end times vs time
    By khoadphamm in forum Excel General
    Replies: 14
    Last Post: 12-31-2019, 02:57 PM
  2. Replies: 6
    Last Post: 01-30-2014, 06:58 AM
  3. [SOLVED] Work hours calculating start and end times
    By hiddenupnorth in forum Excel General
    Replies: 6
    Last Post: 04-20-2012, 01:54 PM
  4. Calculating Start and End times
    By jame24 in forum Excel General
    Replies: 26
    Last Post: 03-16-2012, 02:10 AM
  5. Replies: 7
    Last Post: 10-20-2011, 01:43 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1