Finding Total Duration Based on Multiple Criteria

[Orion19]

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!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Session Length[/TD]
[/TR]
[TR]
[TD]Project 1
[/TD]
[TD]5/1[/TD]
[TD]8:29[/TD]
[TD]11:33[/TD]
[TD]3:04[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]5/1[/TD]
[TD]12:47[/TD]
[TD]15:00[/TD]
[TD]2:13[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]5/1[/TD]
[TD]8:29[/TD]
[TD]11:33[/TD]
[TD]3:04[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]5/1[/TD]
[TD]12:47[/TD]
[TD]15:00[/TD]
[TD]2:13[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]5/1[/TD]
[TD]8:29[/TD]
[TD]11:33[/TD]
[TD]3:04[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]5/1[/TD]
[TD]12:47[/TD]
[TD]15:00[/TD]
[TD]2:13[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

=SUMIFS($E$2:$E$7,$A$2:$A$7,H1,$B$2:$B$7,I1)

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

 

[Peter_SSs]

.. or if you were trying to do it without calculating the individual session lengths, then perhaps like this.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Project	Date	Start Time	End Time		Project	Date	Duration
2	Project 1	01-May	8:29	11:33		Project 1	01-May	5:17
3	Project 1	01-May	12:47	15:00
4	Project 2	01-May	8:29	11:33
5	Project 2	01-May	12:47	15:00
6	Project 3	01-May	8:29	11:33
7	Project 3	01-May	12:47	15:00
Duration

 

[Orion19]

=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.

 

[Orion19]

.. or if you were trying to do it without calculating the individual session lengths, then perhaps like this.

Duration

	A	B	C	D	E	F	G	H
Project		Project
Project 1		Project 1
Project 1
Project 2
Project 2
Project 3
Project 3

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]Date[/TD]
[TD="align: right"]Start Time[/TD]
[TD="align: right"]End Time[/TD]

[TD="align: right"]Date[/TD]
[TD="align: right"]Duration[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]01-May[/TD]
[TD="align: right"]8:29[/TD]
[TD="align: right"]11:33[/TD]

[TD="align: right"]01-May[/TD]
[TD="align: right"]5:17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]01-May[/TD]
[TD="align: right"]12:47[/TD]
[TD="align: right"]15:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]01-May[/TD]
[TD="align: right"]8:29[/TD]
[TD="align: right"]11:33[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]01-May[/TD]
[TD="align: right"]12:47[/TD]
[TD="align: right"]15:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]01-May[/TD]
[TD="align: right"]8:29[/TD]
[TD="align: right"]11:33[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]01-May[/TD]
[TD="align: right"]12:47[/TD]
[TD="align: right"]15:00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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!

 

[Aladin Akyurek]

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?

 

[Orion19]

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.

 

[Aladin Akyurek]

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#D9E1F2"]Project[/td][td="bgcolor:#D9E1F2"]Project 1[/td][/tr]
[tr][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td="bgcolor:#D9E1F2"]Row Labels[/td][td="bgcolor:#D9E1F2"]Sum of Session Length[/td][/tr]
[tr][td]

4​

[/td][td]1-May[/td][td]

5:17​

[/td][/tr]
[tr][td]

5​

[/td][td="bgcolor:#D9E1F2"]Grand Total[/td][td="bgcolor:#D9E1F2"]

5:17​

[/td][/tr]
[/table]

 

[Orion19]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD="bgcolor: #D9E1F2"]Project[/TD]
[TD="bgcolor: #D9E1F2"]Project 1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD="bgcolor: #D9E1F2"]Row Labels[/TD]
[TD="bgcolor: #D9E1F2"]Sum of Session Length[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]1-May[/TD]
[TD]

5:17​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD="bgcolor: #D9E1F2"]Grand Total[/TD]
[TD="bgcolor: #D9E1F2"]

5:17​

[/TD]
[/TR]
</tbody>[/TABLE]

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!

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Row/
Col[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Session Date[/TD]
[TD="align: center"]Session Start Time[/TD]
[TD="align: center"]Session End Time[/TD]
[TD="align: center"]Session Length[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]1/1/18[/TD]
[TD]1/1/18 8:13[/TD]
[TD]1/1/18 10:50[/TD]
[TD]2:37[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]3/26/18[/TD]
[TD]3/26/18 7:55[/TD]
[TD]3/26/18 11:30[/TD]
[TD]3:35[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]3/26/18[/TD]
[TD]3/26/18 13:03[/TD]
[TD]3/26/18 14:57[/TD]
[TD]1:54[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]1/1/18[/TD]
[TD]1/1/18 8:33[/TD]
[TD]1/1/18 10:50[/TD]
[TD]2:37[/TD]
[/TR]
</tbody>[/TABLE]

 

[snjpverma]

in which column/cell do you want your output(s)?