Date and time calculation excluding breaks, weekends for a time period

[Pubhub]

Hello all,

I am truly having a moment and struggling with this.

I have a date/time in this format: 31/01/2017 09:00 in C5 and trying to get a desired finish date in the same format in C6.

This is based on the number of hours to complete the task in a number format in F5. However I want to take in account weekends, breaks and meetings in order to get an accurate completion time and date. A28-C33 contains the below:

08:00 Start
08:00 08:15 Meeting
10:00 10:15 Morning Break
12:45 13:15 Lunch
16:15 16:30 Meeting
16:30 Finish

Basically Monday-Friday start at 08:15 and finish 16:15 with a 15min break at 10am and 30min lunch at 12:45.

For example
C5-30/01/2017 14:00 F5-4 Therefore C6 should be 31/01/2017 10:00

Any assistance would be greatly appreciated, my mind is getting mixed up.

Peter

[ImranBhatti]

Peter you explained well.But it would be better for the others to look at a readymade workbook (with desensitized data) so that the possible solutions could be applied quickly without out typing the data again.

[Pubhub]

@ImranBhatti I was just in the process of adding the attached blank document to assist.

The one additional which I would end up doing manually is overtime. Sometimes a 07:00 start or a 17:30 finish. But this would give a basis and would drastically reduce manual calculation.

Many thanks on your response.

Peter