Finding Total Duration Based on Multiple Criteria

Orion19

Board Regular
Joined
Dec 18, 2017
Messages
56
Hello all, and Thank You in advance!

A sample data set is below. I'm trying to write a formula that tells me the total session length for any given date. In the data set below, the output I'm looking for would be 5:17 (3:04 + 2:13). There are only two sessions, one from 8:29 to 11:33 and the second from 12:47 to 15:00. Projects (name and quantity), Start Times, and End Times will all vary. All of the attempts I've made have failed to recognize there are only two sessions in an example like this. Thanks again for any tips!

ProjectDateStart TimeEnd TimeSession Length
Project 1
5/18:2911:333:04
Project 15/112:4715:002:13
Project 25/18:2911:333:04
Project 25/112:4715:002:13
Project 35/18:2911:333:04
Project 35/112:4715:002:13

<tbody>
</tbody>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
.. or if you were trying to do it without calculating the individual session lengths, then perhaps like this.

Excel Workbook
ABCDEFGH
1ProjectDateStart TimeEnd TimeProjectDateDuration
2Project 101-May8:2911:33Project 101-May5:17
3Project 101-May12:4715:00
4Project 201-May8:2911:33
5Project 201-May12:4715:00
6Project 301-May8:2911:33
7Project 301-May12:4715:00
Duration
 
Upvote 0
=SUMIFS($E$2:$E$7,$A$2:$A$7,H1,$B$2:$B$7,I1)

where H1 = Project 1 and I1 = 5/1.

Thank you for the reply! Is it possible to do this without referencing the project name? I think that's where I'm struggling. On any given date the name and number of projects will be different so I'll need to pivot off of the date and ignore the project names. Some dates won't have the referenced project so it will not find the session lengths for that day.
 
Upvote 0
.. or if you were trying to do it without calculating the individual session lengths, then perhaps like this.

Duration

ABCDEFGH
1ProjectDateStart TimeEnd Time ProjectDateDuration
2Project 101-May8:2911:33 Project 101-May5:17
3Project 101-May12:4715:00
4Project 201-May8:2911:33
5Project 201-May12:4715:00
6Project 301-May8:2911:33
7Project 301-May12:4715:00

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:86px;"><col style="width:69px;"><col style="width:82px;"><col style="width:78px;"><col style="width:31px;"><col style="width:78px;"><col style="width:64px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
H2=SUMPRODUCT(--(A$2:A$7=F2),--(B$2:B$7=G2),D$2:D$7-C$2:C$7)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Thank you for the reply! I haven't worked with SUMPRODUCT and didn't realize how flexible it can be. Unfortunately, I'll have to ignore any references to the project name because the name and number of projects change from date to date. If I reference a project that wasn't worked on that day it'll return 0:00:00. Is there a way to get that information knowing only the date? I can filter the raw data by date and see the pattern but can't seem to flesh it out with a formula. Thank you again!
 
Upvote 0
Thank you for the reply! Is it possible to do this without referencing the project name? I think that's where I'm struggling. On any given date the name and number of projects will be different so I'll need to pivot off of the date and ignore the project names. Some dates won't have the referenced project so it will not find the session lengths for that day.

If you ignore project name and use only the date, we would get:

15:51

not

5:17

Is that what you want?
 
Upvote 0
If you ignore project name and use only the date, we would get:

15:51

not

5:17

Is that what you want?

No, I'm still looking for the 5:17. If I were to filter my data for 01-May I'd see something like the sample data set I posted. Looking at that I can see several projects were worked on during that day. However, I can see that two intervals repeat in the Start and End Time columns (8:29-11:33 AND 12:47-15:00). That tells me that on 01-May there were two sessions that happened and they occurred during those intervals. If I were to add them up I'd know how much time was spent actually working on 01-May (5:17). Adding up all of the intervals gives the 15:51 but that doesn't tell me anything because it ignored the fact that the intervals are repeating. I'm looking for the total amount of time spent working on any given day.
 
Last edited:
Upvote 0
No, I'm still looking for the 5:17. If I were to filter my data for 01-May I'd see something like the sample data set I posted. Looking at that I can see several projects were worked on during that day. However, I can see that two intervals repeat in the Start and End Time columns (8:29-11:33 AND 12:47-15:00). That tells me that on 01-May there were two sessions that happened and they occurred during those intervals. If I were to add them up I'd know how much time was spent actually working on 01-May (5:17). Adding up all of the intervals gives the 15:51 but that doesn't tell me anything because it ignored the fact that the intervals are repeating. I'm looking for the total amount of time spent working on any given day.

Not sure I understand... Perhaps you can work with a pivot table...

Row\Col
A​
B​
1​
ProjectProject 1
2​
3​
Row LabelsSum of Session Length
4​
1-May
5:17​
5​
Grand Total
5:17
 
Upvote 0
Not sure I understand... Perhaps you can work with a pivot table...

Row\Col
A​
B​
1​
ProjectProject 1
2​
3​
Row LabelsSum of Session Length
4​
1-May
5:17​
5​
Grand Total
5:17

<tbody>
</tbody>


Sorry, I'm having a hard time describing my data set. I gave the pivot table a shot but have the same problem. The value in "Sum of Session Length" gives me the sum of all session lengths for each date. I need it to ignore repeating values and sum only unique values. Here's a better data set below. I need a formula smart enough to tell that 2:37 were worked on 1/1/18 and 5:29 were worked on 3/26/18. I only want to add together the difference between unique Session End Times and unique Session Start Times. I have repeating data so I need it to acknowledge that you can only work the same time slot once on a given day.

Thanks again for your patience and willingness to help!

Row/
Col
ABCD
1Session DateSession Start TimeSession End TimeSession Length
21/1/181/1/18 8:131/1/18 10:502:37
33/26/183/26/18 7:553/26/18 11:303:35
43/26/183/26/18 13:033/26/18 14:571:54
51/1/181/1/18 8:331/1/18 10:502:37

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top