Exclude time difference from a date range

JaiPrakash

New Member
Joined
Aug 7, 2017
Messages
6
I'm trying to exclude duration of a date range from a set of range that is in another sheet:

Table_1 shows total time in seconds a ticket was assigned to someone
Table_2 shows the time in seconds when the ticket was put on hold

Table_1

ID
Start_Date
End_Date
Duration_In_Seconds
Final_Time_After_Exclusion
INC1
2/18/2020 15:142/22/2020 1:14295200
INC_3
2/18/2020 14:232/24/2020 17:30529620

Table_2

IDStart_DateEnd_Date
INC_32/18/2020 12:212/24/2020 16:08
INC_12/18/2020 15:232/22/2020 1:08
INC_12/22/2020 17:442/24/2020 19:24

Need your help with a formula that exclude time if date range in Table_2 falls between date range present in Table_1.

I tried Index and match but wasn't able to come up with a logic to get this done.
Thanks so much for all the help!!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
INC_3 seems to have been put on hold before it was assigned to someone.
INC_1 seems to have been put on hold after the end date.

Are those conditions possible?
 

JaiPrakash

New Member
Joined
Aug 7, 2017
Messages
6
Hello DRSteele,

Apologies for not been able to respond earlier.

Yes, there are instances where it is put on hold before it assigned to someone. If those case what I'm trying to do is to exclude time when the ticket was assigned to an assignee till the time it was on hold. #INC_3 > time to be excluded = 2/18/2020 14:23 till 2/24/2020 16:08.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,095
Messages
5,622,670
Members
415,921
Latest member
ExcelNoob28

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
Top