worksheet function - Time Elapsed In Hours Between Multiple Dates In Excel

I am trying to find a formula that works out the business hours between two dates in excel.

The business hours start at 09:00 and end at 17:00 and do not include weekends.

The spreadsheet is currently laid out in the following way;

A1: Start Time       -       B1: End Time   -       C1: Total Business hours
A2: 2016-01-04 10:31:17   -  B2: 2016-01-06 10:02:14

Is there a formula I can use that will calculate the difference in time (business hours) and display it in C2?

Answer

Assuming that the dates are in the same year and neglecting a small round off error I've found a viable solution:

I'll first explain it and then add the formula below.

I've split the solution in multiple instances if's

IF 1: DATES IN SAME WEEK

IF(WEEKNUMBER(A2)=WEEKNUMBER(B2);

IF 1.1: DATES ON SAME DAY

IF(DAY(A2)=DAY(B2));

Take the hours of end date minus hours of start date = result

(B2-A2)*24;

IF 1.2: DATES NOT ON SAME DAY

Take the day of the end date minus the day of the begin date (=the days between both dates) and multiply by 8 (=17-9) and add the difference in hours. The MOD function is used to cut off the days of the dates and only remain with the time.

(DAY(B2)-DAY(A2))*(17-9)+(MOD(B2,1)-MOD(A2,1))*24);

IF 2: DATES NOT IN SAME WEEK

I calculate the number of hours of the weeks in between, then I add the hours of the start date to the end of the start week (luckily the year started on a friday so I could just do weeknumber(startdate)*7 and finally I add the number of hours from the start of the week to the end date.

(WEEKNUMBER(B2)-WEEKNUMMER(A2)-1)*5*(17-9)+(WEEKNUMBER(A2)*7-QUOTIENT(A2;1))*(17-9)+17+(QUOTIENT(B2;1)-(WEEKNUMBER(B2)*7-4))*(17-9)+(MOD(B2;1)-MOD(A2;1))*24-9)

At the end, you just need to put all these pieces of code together and you can calculate the business hours between dates.