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?
Best 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 1.1: DATES ON SAME DAY
Take the hours of end date minus hours of start date = result
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.
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.
At the end, you just need to put all these pieces of code together and you can calculate the business hours between dates.