+ Reply to Thread
Results 1 to 5 of 5

Calculating total time spent by half hour

  1. #1
    Registered User
    Join Date
    03-12-2014
    Location
    Salt Lake, Ut
    MS-Off Ver
    Excel 1013
    Posts
    3

    Calculating total time spent by half hour

    Hi,
    I cannot figure out a formula to determine how to calculate time spent in a half hour interval, when the time is overlapping. To exemplify, [Cell A1] has a start time of 12:07am: [Cell b2] has an end time of 12:37 am. I need to calculate how much time of this duration was spent between 12:00am and 12:30 am; 12:30 am and 1:00 am. Obviously, I can do this manually. However, I have thousands of rows that I need to calculate the duration of certain states in a call center were spent by half hour interval. Any advise would be greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: Calculating total time spent by half hour

    This can be done, can you please upload a sample file... how you want to present this.

    Based on which the formula may change.

    Thanks,
    Like my answer, then click * below

  3. #3
    Registered User
    Join Date
    03-12-2014
    Location
    Salt Lake, Ut
    MS-Off Ver
    Excel 1013
    Posts
    3

    Re: Calculating total time spent by half hour

    I've posted an Image below. I am thinking the half hour intervals go horizontal. So in row 2 would show how much time equates for each half hour. If there's a better way to do it. I'm totally open. However, the Start time and End time are like this.

    A B C D E F
    1 Start_Date End_Date 12:00 AM 12:30 AM 1:00 AM 1:30 AM etc…
    2 12:13:27 AM 12:13:51 AM
    3 12:15:56 AM 12:16:13 AM
    4 12:26:42 AM 12:27:52 AM
    5 12:30:21 AM 12:31:18 AM
    6 12:33:29 AM 12:33:40 AM
    7 12:39:13 AM 12:39:24 AM
    8 12:41:42 AM 12:42:11 AM
    9 12:44:38 AM 12:45:00 AM
    10 12:52:26 AM 12:52:43 AM

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

    Re: Calculating total time spent by half hour

    Please post a sample workbook so members don't have to recreate new ones.
    To attach a Workbook
    (please do not post pictures of worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    03-12-2014
    Location
    Salt Lake, Ut
    MS-Off Ver
    Excel 1013
    Posts
    3

    Re: Calculating total time spent by half hour

    Sorry about that. It should be attached now.
    Attached Files Attached Files

+ 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. Calculating Number of Workers per Half Hour
    By vioravis in forum Excel General
    Replies: 1
    Last Post: 10-14-2015, 09:55 PM
  2. Excel not calculating time when 18:00 used and total is one hour or less
    By gbrsml in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-03-2014, 08:34 PM
  3. [SOLVED] Adding half an hour to a time value
    By DaZombie in forum Excel General
    Replies: 7
    Last Post: 10-25-2010, 11:41 AM
  4. Replies: 1
    Last Post: 08-10-2010, 02:13 AM
  5. [SOLVED] Calculate time difference to the half hour
    By Ken Ivins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 03:05 PM

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