Difference between two dates in excel 2013

Question

I want to calculate the time difference between two dates with the following criteria:

1) excluding sundays and public holidays

2) TIMING: Mon to Fri = 7 AM to 7 PM & Sat = 7 AM to 3 PM

I tried a many formulas and nothing works for me. please any ideas??

Eg: Start Time: 3/6/2015 10:00 AM

End Time: 4/6/2015 12:00 PM

Then I should get the difference as 13 hrs (excluding time out of business hours)

Answer 1

If start and end "timestamps" will always be within working hours then you can use a formula like this:

=(SUM(NETWORKDAYS.INTL(A2,B2,{1,"1111101"},H$2:H$10)*{12,8})-IF(WEEKDAY(B2)=7,8,12))/24+MOD(B2,1)-MOD(A2,1)

Assuming start date/time in A2, end date/time in B2 and holiday list in H2:H10

Format result cell as [h]:mm and if start time is today (Monday) at 14:00 and end time is exactly a week later you will get the result 68:00 (5*12 hour days and 1*8 hour Saturday)