Hi All,

Can someone help me for the below requirement. I have already tried multiple formula's but not getting expected result.

I want to calculate net working hours between two dates considering start shift time, end shift time, start date & time, end date & time, weekends and holidays.

For Ex.
Shift Start Time : 18:30:00 PM
Shift End Time : 04:30:00 AM
Start Date & Time: 05/01/2016 8:50:00 PM
End Date & Time : 05/01/2016 11:14:02 PM
Weekend: Saturday & Sunday
Holiday: List

I have tried below formula's but not get correct result.

1. =IF(INT(D3)=INT(E3),IF(AND(WEEKDAY(D3,2)<6,COUNTIF(HoliDays,D3)=0),MEDIAN(E3,INT(E3)+Start,INT(E3)+End)-MEDIAN(D3,INT(D3)+Start,INT(D3)+End),0),MAX(WORKDAY(E3,-1,HoliDays)-WORKDAY(D3,1,HoliDays)+1,0)*9/24+IF(AND(WEEKDAY(D3,2)<6,COUNTIF(HoliDays,D3)=0),MAX(End-MOD(D3,1),0),0)+IF(AND(WEEKDAY(E3,2)<6,COUNTIF(HoliDays,E3)=0),MAX(MOD(E3,1)-Start,0),0))

2. =IF(NETWORKDAYS(D12,E12)<2,MOD(E12,1)-MOD(D12,1),($E$1-MOD(D12,1)+MOD(E12,1)-$D$1)+((NETWORKDAYS(D12,E12)-2)*10/24))

Below is some example data set
Start Date End Date
05/01/16 20:50 05/01/16 23:14
05/01/16 21:32 05/01/16 23:42
05/01/16 21:32 05/01/16 23:43
05/01/16 21:28 05/01/16 23:44
05/01/16 21:38 05/01/16 23:47
05/01/16 21:36 05/01/16 23:49
05/01/16 21:40 05/01/16 23:51
05/01/16 21:40 05/01/16 23:54
05/01/16 21:41 05/01/16 23:55
05/01/16 21:43 05/01/16 23:59
05/01/16 21:55 06/01/16 23:59
05/01/16 21:57 06/01/16 0:30
05/01/16 22:07 06/01/16 0:32
05/01/16 22:12 06/01/16 0:36
05/01/16 22:13 06/01/16 0:37
05/01/16 22:16 06/01/16 0:38
05/01/16 22:16 06/01/16 0:39
05/01/16 22:18 06/01/16 0:41
05/01/16 22:17 06/01/16 0:42
05/01/16 22:18 06/01/16 0:45
05/01/16 22:19 06/01/16 0:46
05/01/16 22:22 06/01/16 0:48
05/01/16 22:18 06/01/16 0:50
05/01/16 22:30 06/01/16 0:51
05/01/16 22:32 06/01/16 0:52
05/01/16 22:32 06/01/16 0:53
05/01/16 22:36 06/01/16 0:54
05/01/16 22:40 06/01/16 0:55

Need help on urgent basis.

It would be great if shared the excel file with formula.

If possible please explain logic.

Thanks a lot for the help in advance.

Upendra