# Calculating total time spent by half hour

1. ## 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. ## 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,

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. ## 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).
• 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.

5. ## Re: Calculating total time spent by half hour

Sorry about that. It should be attached now.

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

#### 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