calculate the time between two dates excluding weekends

**Dumy** · 06-30-2011, 05:48 AM

I need to calculate how much time has passed since starting the driver on the way to return back.
The start date does not matter for 8 hours, ending date also does not matter for 8 hours
All the days between the start and end date are counted as 8 hours (standard hours)
Note: to exclude weekend days
Which formula can be obtained in a single cell during the calculation carried out on the road

**tarquinious** · 07-01-2011, 07:08 AM

Whew! that took me a bit of messing about, but I got there in the end. Others may have a more clever and graceful way to get the result, I just use brute force.

I have come up with the following formula for you to place in cell C2 (where A2 has the start date/time combo, and B2 has the finish date/time combo):

Please Login or Register  to view this content.

To explain this formula:

Please Login or Register  to view this content.

...calculates the number of full working days (excluding weekends) and multiplies that by 8 hours in a day

Because the days may not be full days, we need to take the number of hours before the end of the working day (16:00 in this case) off the result of above:

Please Login or Register  to view this content.

And then finally take away the number of hours since the start of the day (in this case 08:00) before work commenced.

Please Login or Register  to view this content.

In your example of Start: 01/06 11:00 and finish 06:06 14:00 this results in:
4 working days (4th & 5th are a weekend) multiplied by 8 hours = 32
...minus the 2 hours at the end of the day for finishing at 14:00
...minus the 3 hours at the start of the day for not starting until 11:00
...results in 27 hours