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]

In A1:B1 of the pivot table, B1 contains a filter: you can choose for any project or for all. The pivot table shows the appropriately summed ssession length.

If this does not meet your needs, try to post a sample input along with the output you want to obtain from that input.

 

[Orion19]

Sorry, hopefully this data set makes more sense. I'm looking for formulas for G2:G3 below. Thank you!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

Row/
Column​

[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/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]
[TD][/TD]
[TD="align: center"]Dates[/TD]
[TD="align: center"]Total Worked[/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]
[TD][/TD]
[TD]1/1/18[/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]
[TD][/TD]
[TD]3/26/18[/TD]
[TD]5:29[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]1/1/18[/TD]
[TD]1/1/18 8:13[/TD]
[TD]1/1/18 10:50[/TD]
[TD]2:37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[snjpverma]

I used the dates in Column A as a reference. I didn't re-write the dates in Column F.
Don't be confused by the dates, I have used the dates as per DD/MM/YYYY as per the Indian timings. It doesn't affect the formula in any way.

Try this

	A	B	C	D	E	F	G
Date	Start	End	Length			Output

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:73px;"><col style="width:109px;"><col style="width:109px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]01-01-2018[/TD]
[TD="align: right"]01-01-2018 08:13[/TD]
[TD="align: right"]01-01-2018 10:50[/TD]
[TD="align: right"]02:37[/TD]

[TD="align: right"]02:37[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]26-03-2018[/TD]
[TD="align: right"]26-03-2018 07:55[/TD]
[TD="align: right"]26-03-2018 11:30[/TD]
[TD="align: right"]03:35[/TD]

[TD="align: right"]05:29[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]26-03-2018[/TD]
[TD="align: right"]26-03-2018 13:03[/TD]
[TD="align: right"]26-03-2018 14:57[/TD]
[TD="align: right"]01:54[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]01-01-2018[/TD]
[TD="align: right"]01-01-2018 08:33[/TD]
[TD="align: right"]01-01-2018 10:50[/TD]
[TD="align: right"]02:37[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 {=SUMIF(A2:INDEX(A2:A5,MATCH(FALSE,(INT(B2:B5)=INT(B2)),0)-1),INT(B2),D2:D5)} G3 {=SUMIF(A3:INDEX(A3:A6,MATCH(FALSE,(INT(B3:B6)=INT(B3)),0)-1),INT(B3),D3:D6)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Sorry, hopefully this data set makes more sense. I'm looking for formulas for G2:G3 below. Thank you!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

Row/
Column​

[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/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]
[TD][/TD]
[TD="align: center"]Dates[/TD]
[TD="align: center"]Total Worked[/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]
[TD][/TD]
[TD]1/1/18[/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]
[TD][/TD]
[TD]3/26/18[/TD]
[TD]5:29[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]1/1/18[/TD]
[TD]1/1/18 8:13[/TD]
[TD]1/1/18 10:50[/TD]
[TD]2:37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Looks like...

In G2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$3:$A$6=F3,MATCH($B$3:$B$6,$B$3:$B$6,0)),ROW($A$3:$A$6)-ROW($A$3)+1),$D$3:$D$6))

 

[snjpverma]

Alternatively

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Start	End	Length			Output
2	01-01-2018	01-01-2018 08:13	01-01-2018 10:50	02:37			02:37
3	26-03-2018	26-03-2018 14:57	26-03-2018 11:30	03:35			05:29
4	26-03-2018	26-03-2018 14:57	26-03-2018 14:57	01:54
5	01-01-2018	01-01-2018 08:13	01-01-2018 10:50	02:37
Sheet

 

[Orion19]

Thank you!!! This works well!!

 

[Orion19]

Thank you!!! This does the trick! I think I need to develop a better understanding of MATCH, FREQUENCY, AND INDEX functions. Thank you again!

Looks like...

In G2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$3:$A$6=F3,MATCH($B$3:$B$6,$B$3:$B$6,0)),ROW($A$3:$A$6)-ROW($A$3)+1),$D$3:$D$6))

 

[Aladin Akyurek]

Thank you!!! This does the trick! I think I need to develop a better understanding of MATCH, FREQUENCY, AND INDEX functions. Thank you again!

You are welcome. Thanks for the update.

 

[Orion19]

Looks like...

In G2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$3:$A$6=F3,MATCH($B$3:$B$6,$B$3:$B$6,0)),ROW($A$3:$A$6)-ROW($A$3)+1),$D$3:$D$6))

Quick question for you again if you're available. I filled about 500 cells with appropriate variations of the formula you came up with. The values are returned correctly and the macro runs smoothly. However, if I manipulate the raw data set in any way there is a delay of over a minute before the workbook responds. For example, if I filter out a specific date or add a row it will cause the delay. I *think* it's running the array formulas again to auto update but I'm not certain. Do you have any tips? Thank you!!

 

[Aladin Akyurek]

Quick question for you again if you're available. I filled about 500 cells with appropriate variations of the formula you came up with. The values are returned correctly and the macro runs smoothly. However, if I manipulate the raw data set in any way there is a delay of over a minute before the workbook responds. For example, if I filter out a specific date or add a row it will cause the delay. I *think* it's running the array formulas again to auto update but I'm not certain. Do you have any tips? Thank you!!

A huge of number of this kind of formulas can cause some delay, especially if you also have formulas around with volatile functions like INDIRECT, OFFSET.

Does the data range change often? If it does, we can work with dynamic named ranges. If you want try this route, would you post the formula as you have implemented it?