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.
No comments:
Post a Comment