Find work day duration

[Snoopy2003]

Hello,

I have a table with the following data fields:
Tel Ext
Date
Start Time
End Time

Work day duration for selected date and Tel Ext equals to Max(End Time)-Min(Start Time).
How can I calculate it within the worksheet ?

I attached an excel workbook with data example.

Thank you for your help

[Søren Larsen]

Perhaps:

In J5: =MAX(IF(($A$4:$A$13=J3)*($B$4:$B$13=J4),$D$4:$D$13)) and confirmed with Ctrl+Shift+Enter
In J6: =MIN(IF(($A$4:$A$13=J3)*($B$4:$B$13=J4),$C$4:$C$13)) and confirmed with Ctrl+Shift+Enter
In J7: =J5-J6

You may have to change , to ; depending on your delimiters.

??

[Paul]

I think your second formula is meant to be the MIN Start Time (based on the post text, even though the spreadsheet says MIN end time).

J5: =MAX(IF(A1:A20=J3,IF(B1:B20=J4,D1:D20)))
J6: =MIN(IF(A1:A20=J3,IF(B1:B20=J4,C1:C20)))
J7: =J5-J6

The formulas in J5 and J6 are array formulas and must be confirmed using CTRL+SHIFT+ENTER, not just ENTER. Format J5 and J6 as Time. Format J7 as "h:mm".

[Snoopy2003]

Hello,

I think your second formula is meant to be the MIN Start Time
You are right, sorry for the mistake in the example file.

Søren Larsen and Paul - thank you for your quick response and help.
I tried your suggestion and it worked like a charm.
I tried to solve this for several hours with no success.

Thank you for your time.

[Paul]

We're glad to be of assistance.