Calculate Days, Hours & Minutes Worked

Good Morning

I would welcome any help you guys can offer, with a formula for calculating the time we spend on a particular project.

The rules are:

Each working day starts at 08:30 and finishes at 17:30.
Saturdays are 08:30 through to 12:30.

I require a formula to tell me how many days, hours and minutes worked based on the start date in column A and the end date in column B. Ideally formula should be in C.

For example:

Column (A) : 01/07/15 08:30
Column (B) : 01/07/15 17:30
Column (C) : 8 Hours

For example:

Column (A) : 01/07/15 08:30
Column (B) : 02/07/15 17:30
Column (C) : 1 Day 8 Hours

For example:

Column (A) : 01/07/15 08:30
Column (B) : 04/07/15 12:30
Column (C) : 3 Days 4 Hours

For example:

Column (A) : 01/07/15 08:30
Column (B) : 06/07/15 12:00
Column (C) : 3 Days 7 Hours 30 Minutes

For example:

Column (A) : 01/07/15 08:30
Column (B) : 07/07/15 15:30
Column (C) : 5 Days 3 Hours

Any help you could offer would be Excellent. Many thanks for Looking ..

Mulderman

This is actually a lot more complicated than it first seems. A shift from 0830 to 1730 is actually 9 hours and yet you show 8 hours. I presume you've excluded lunch hour on each day. This works fine on all examples except for the last one, which have a finishing shift at 15:30, which, if I assume lunch to be between 12:30 and 13:30, does not take into account lunch hour on the very last day (hence 3 hours as opposed to 2).

I fail to find any 1 formula that can fit, reasonably well in 1 cell. If you look at the attached, all my methods of calculations ultimately lead to the need of 2 cells. 1 being the total number of work hours - and another for the worded version of that in days, hours and minutes.

Just a brief on the methods I used, the Additive method is one I thought of to build up the number of work hours required per day. The Subtractive method is to take the full time difference between the start and end, and start deducting "outside hours" (e.g. between Monday 1730 to Tuesday 0830, which is 15 hours), taking also into account Saturday half-days and Sundays. There are 2 versions of these two methods, the Breakdown method is one I used with multiple helper cells when building up these methods, the non-Breakdown method is one that I removing all the helper cells and started combining them.

The one I think you should look at in terms of "simplicity" is the Subtractive methods.