Calculate Difference between two dates in hh:mm excluding Weekend and Holidays

[Ombir]

Hi,

I'm not good at complex formulas. I want to calculate the difference between two dates in hh:mm format excluding weekands and Holidays range. Tried Networkdays.Intl but not avail so far.

Input format:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="165"></colgroup><tbody>
[TD="class: xl66, width: 165, align: right"]May 19, 2017 11:07:43 AM
[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 165"]
<colgroup><col width="165"></colgroup><tbody>[TR]
[TD="class: xl66, width: 165, align: right"]May 22, 2017 11:07:26 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<colgroup><col width="165"></colgroup><tbody>[TR]
[TD="class: xl66, width: 165, align: right"]May 19, 2017 11:07:43 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<colgroup><col width="165"></colgroup><tbody>[TR]
[TD="class: xl66, width: 165, align: right"]May 29, 2017 11:07:26 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Holidays can be assumed on May 21,2017, May 25,2017 and weekend as Sat-Sun.

I would really appreciate if anybody can help with me formula without using helper column.

Thanks

 

[Special-K99]

NETWORKDAYS returns only whole days

Put the holiday dates in A4:A5 and use this

=NETWORKDAYS(A1,B1,A4:A5)

Use custom format [h]mm:ss

If you want actual time difference that's more complex, not sure I have time to work that out.

 

[Ombir]

NETWORKDAYS returns only whole days

Put the holiday dates in A4:A5 and use this

=NETWORKDAYS(A1,B1,A4:A5)

Use custom format [h]mm:ss

If you want actual time difference that's more complex, not sure I have time to work that out.

Yeah, I want to find the actual time difference. Below formula doesn't works if holiday falls on Start or End Date.

=B2-A2-(NETWORKDAYS.INTL(A2,B2,"0000000")-NETWORKDAYS(A2,B2,Holidays))

I know its complex but whenever you've free time please give it a go.

Thanks for your time.